VBA macro to Extract date from text string in Excel question?

Question

I am trying to extract the date from a text string for every row in column A, and insert it into the same row in column B. The rows in Column A consist of filenames imported from another macro such as:
FY10 TECH-CON as of 30 November 2010.xlsx
FY11 TECH-CON as of 25 April 2011 for review.xlsx

To accomplish this I have been studying/ experimenting with an excellent VBA macro written by Yahoo Answers contributor garbo7441’s response to a similar question (Garbo’s code is included below). It uses the Find ”/” thingy to extract the date (- 2 characters from “/” forward 10). For instance the macro works perfect if the text string in column A1 were instead: FY10 TECH-CON as of 30/11/2010.xlsx, but not when in the dd MMMM yyyy format. I have tried making it find the “.” which is unique in each row, but as far as I can tell there is no way to account for the value difference (May = 3 characters, September=9).
Any suggestions/help on making this work are greatly appreciated!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, "A").Value <> "" Then
dmark = Application.Find("/", Cells(i, "A"), 1)
If Len(Cells(i, "A")) < 12 Then
Cells(i, "B").Value = Cells(i, "A").Value
Else
Cells(i, "B").Value = Format(Mid(Cells(i, "A"), _
dmark - 2, 10), "mm/dd/yyyy")
End If
End If
Next
End Sub

Answer 1

I think the easiest way would be to do it in two steps.



Step1: Make a loop that finds " January " (make it find the spaces before and after the word too) and replaces it with "/01/". repeat for all 12 months. You might also have to do it all for 3 letter abbreviations.



Step2: run the script you already have.

The advantage of doing this is that it will work for both types of date formats.



[Edit]

Y!A reformats code and chops off code if it gets too long.

So I decided to use an image instead of pasting the code, sorry you will have to retype the code from the image (or use the link at the bottom). This is what the final code looks like for me, and it seems to work fine.



http://img339.imageshack.us/img339/5353/datecleaningcode.jpg

You just have to change the first line "Sub TestingDate" to your old one "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"



If you have dates that use abbreviated months, just change:



ReDim Months(1 to 24)

ReDim Indexes(1 to 24)



Then add in "Jan", "Feb", "Mar" etc etc to the end of the months array.

And add a repeat of "01", "02", "03" etc to the end of the indexes array.



Note: This code will change the data you have in column A (it wont just add stuff to column B). So if you want to keep the original text fields you will need to make a copy of column A before running the code.



You can get it in a txt file here: http://www.filedropper.com/testingdate

Answer 2

Excel Vba String To Date

Answer 3

The same way