CODEWITHSUNDEEP
exceldate
B Peter
B Peter

Reputation: 11

convert a text field to date in excel

I have a sheet that has a text field that contains the following:

Wednesday, October 18th, 2017, 9:30 PM EDT

I need to convert this to a date format. I don't care about the time, I just need it to look like this: 10/18/2017.

Any help would be appreciated.

Upvotes: 1

Views: 55

Answers (3)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Here is one more formula suggestion.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(REPLACE(A1,FIND(",",A1,FIND(",",A1,1)+1)-2,3,"|"),",",REPT(" ",999)),999,999)),"|",",")+0

enter image description here

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

If you want to convert the values in place, then select the cells in question and run this short macro:

Sub INeedADate()
    Dim r As Range, d As Date, s As String, DQ As String
    DQ = Chr(34)

    For Each r In Selection
        ary = Split(r.Text, " ")
        s = DQ & ary(1) & " " & numpart(ary(2)) & ", " & Left(ary(3), 4) & DQ
        r.Formula = "=DATEVALUE(" & s & ")"
        r.NumberFormat = "mm/dd/yyyy"
    Next r
End Sub

Public Function numpart(s) As String
    Dim L As Long, i As Long
    L = Len(s)
    numpart = ""
    For i = 1 To L
        If IsNumeric(Mid(s, i, 1)) Then numpart = numpart & Mid(s, i, 1)
    Next i
End Function

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

parse the string:

 =--(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),2*999,999)),LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),2*999,999)))-3) & " " & TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),999,999)) & " " & LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),3*999,999)),LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),3*999,999)))-1))

Then format as you want.

enter image description here

Upvotes: 1

Related Questions