General discussion

Locked

Extract a date from an Excel text string

By cathag ·
I have this spreadsheet, one of the cells is a text string, it might contain a date, I need to extract this date. I found a VBA function that will work some times. The problem is, in that same text string, there is another piece of data that looks like: "S/N 000-0000-0000". I have found that when this S/N is there, the function would either return a negative date or the wrong value for the date and I do not know how to parse and eliminate this S/N before I extract the date.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Hello

by softcorp.us In reply to Extract a date from an Ex ...

In order for Excel to interpret the contents of a cell as a date, only the date must be in the cell. Excel does not have any way to automatically parse out a date, or something that looks like a date, from mixed data in a single cell.

Sorry...

-----Steve Jackson

CEO/CSA
Software Corporation (Softcorp)

Message was edited by: beth.blakely@...

Collapse -

by cathag In reply to Hello

Poster rated this answer.
Excel functions cannot do that, but I am using a BVA module that allows me to extract the date. The help I need is parsing and extracting a different string that when present makes this module fail.
The module I am using is located at http://www.igetit.net/SampleCode/Excel_vba_code.asp
It works for the date but, as I explained, if in the same cell I do have something of the sort S/N ###-####-#### This function will return a negative value for the date or some different date (I pressume taken from the S/N code)

Collapse -

by chitosunday In reply to Extract a date from an Ex ...

Where is the date in "S/N 000-0000-0000". Kindly give us the complete information. If that is the only data, what result to you want it to be ? o or a text string "Not a date"

Collapse -

by cathag In reply to

Poster rated this answer.
The content of the cell in question is:
"S/N 370-2403-0080 ARO V3.08 Memory chips U4 & U5 were swapped. Placed them in their correct positions and item passed all tests. 01/20/04 LA"
From this text, I need to extract the date and the code I got from http://www.igetit.net/SampleCode/Excel_vba_code.asp works only if I do not have a string of the kind "370-2403-0080" I apparently interprets this as a date and gives me a negative date or the wrong answer. What I am trying right now is to remove the S/N from the string before the date is parsed so I will get the right information. I appreciate any other idea or a piece of code that will help me do this.

Collapse -

by cathag In reply to Extract a date from an Ex ...

Point value changed by question poster.

Collapse -

by chitosunday In reply to Extract a date from an Ex ...

Function GetDate(strInput As String) As Date
Dim DateFormat() As String
Dim intDateLength As Integer
Dim intMaxFormat As Integer
Dim intFrmtCtr As Integer
Dim intPosition As Integer

intMaxFormat = 6
ReDim DateFormat(1 To intMaxFormat)

DateFormat(1) = "*##[-/]##[/-]####*"
DateFormat(2) = "*#[-/]##[-/]####*"
DateFormat(3) = "*##[-/]#[-/]####*"
DateFormat(4) = "*##[-/]##[-/]##*"
DateFormat(5) = "*#[-/]##[-/]##*"
DateFormat(6) = "*#[-/]#[-/]##*"

GetDate = Now

For intFrmtCtr = 1 To intMaxFormat

If strInput Like DateFormat(intFrmtCtr) Then


intDateLength = Len(DateFormat(intFrmtCtr)) - 8


strInput = Replace(strInput, " ", "")



For intPosition = 1 To Len(strInput)

If Mid(strInput, intPosition, intDateLength) Like DateFormat(intFrmtCtr) Then
GetDate = DateValue(Mid(strInput, intPosition, intDateLength))
Exit Function
End If

Next intPosition

End If

Next intFrmtCtr

End Function

Collapse -

by cathag In reply to

Poster rated this answer.
Gorgeous!!
Thanks a lot sir.
Cathalina

Collapse -

by cathag In reply to Extract a date from an Ex ...

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums