Discussion Locked

Extract a date from an Excel text string

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

8 replies

by softcorp.us

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@...

by cathag

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)

by chitosunday

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"

by cathag

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.

by chitosunday

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

This discussion is closed to new replies.