Extract Date from text

[Gregfox]

Hi all,
I have the following TEXT(not date format):

Stocks - Market value = $1,00.00* Tue Jan 30 2018 4:49:33 pm EDT in Cell A1 sheet 1

I wish to extract the date only to be 1/30/2018 in sheet 2 in cell B3

Note: the dates change like Feb 21 2017, Apr 4 2016 etc.


I tried the MID function after the '*' to no avail.

 

[Rick Rothstein]

Assuming there is always as space after the asterisk, use this formula...

=0+REPLACE(MID(A1,FIND("*",A1)+6,11),7,0,",")

and format the cell you put it in with the date format you want for it.

 

[AlphaFrog]

Fix for single digit days...

=0+SUBSTITUTE(REPLACE(MID(A1,FIND("*",A1)+6,11),7,0,",")," ,",", ")

 

[Rick Rothstein]

Fix for single digit days...

=0+SUBSTITUTE(REPLACE(MID(A1,FIND("*",A1)+6,11),7,0,",")," ,",", ")

I forgot about single digit days

I have a simpler fix...

=0+REPLACE(MID(A1,FIND("*",A1)+6,11),7,0,", ")

 

[Gregfox]

Thank you for the reply.
When I put this into my VBA code it fails. I thought when Iposted this in the VBA Forum it would be understood as VBA code, my Bad.
I should have indicated VBA and PC windows 10, Excel 2007.Sorry.

 

[kvsrinivasamurthy]

VBA code

Sub Macro1()
Ary = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")


k = InStr(1, Range("A3"), "*") + 6


For Mth = 0 To 12
If Mid(Range("A3"), k, 3) = Ary(Mth) Then Exit For
Next


D_ay = 0 + Trim(Mid(Range("A3"), k + 4, 2))
Yr = 0 + Right(Trim(Mid(Range("A3"), k, 11)), 4)


Range("B3") = DateSerial(Yr, Mth, D_ay)
End Sub

 

[Gregfox]

Looks good, but I get an error on the last line Range ("B3") = DateSerial etc.

 

[Greg Truby]

Rick already solved this for you. Just translate what he gave you to VBA:

Sub Foo()

    Dim dtmDate As Date, _
        strDate$
    
    Let strDate = Sheet1.Range("A1").Text
    Let dtmDate = DateValue(WorksheetFunction.Replace(Mid(strDate, InStr(1, strDate, "*") + 6, 11), 7, 0, ", "))
    Sheet2.Range("B3") = dtmDate
    
    MsgBox Format(dtmDate, "dd-mmm-yy"), vbInformation, "Rick's a Stud"



End Sub

 

[Rick Rothstein]

Thank you for the reply.
When I put this into my VBA code it fails. I thought when Iposted this in the VBA Forum it would be understood as VBA code, my Bad.

This is not a VBA Forum, rather, it is just an Excel Forum where questions about Excel can be asked. Anyway, here is the code I posted in Message #2 translated into a VBA macro...

[table="width: 500"]
[tr]
	[td]Sub GetDateFromCellA1()
  Sheets("Sheet2").Range("B3") = CDate(Evaluate("REPLACE(MID(Sheet1!A1,FIND(""*"",Sheet1!A1)+6,11),7,0,"", "")"))
End Sub[/td]
[/tr]
[/table]

 

[Greg Truby]

Jeepers, I was kinda 'xpectin' ol' Rick ta say sumpin' 'bout the title of my message box -- but nuttin'.