Excel 2010 : extract month day year from text

[wingfield]

hello,

I have a column of dates that is in the format mm/dd/yyyy. unfortunately, the column is in text. also, the text has a number of spaces before the date. for example, a cell could have "space space space space 4/15/1910". another cell could have "space space 12/1/1900".

I need to extract the month, day, and year and then have them reformatted and output to 2 columns.

1st column is eventdate in varchar(50) which looks like dd-Apr-yyyy (example 15 Apr 1910)
2nd column is date format which looks like yyyy-mm-dd (example 1910-04-15)

oh, and then I need to copy that formula down until it reached the end of how ever many rows there are.

any help would be appreciated.

david

 

[VoG]

To convert the text to date

=DATEVALUE(TRIM(A1))

and format as Date.

 

[wingfield]

To convert the text to date

=DATEVALUE(TRIM(A1))

and format as Date.

thank you for such a quick response. I used that formula; and it gives #VALUE!.

any other thoughts? I was thinking something with mid, find and len perhaps??

david

 

[VoG]

Perhaps your spaces are the dreaded CHAR(160).

Try selecting the dates then run Rearranging Data in Columns

 

[wingfield]

I don't believe that will work. the dates are very old and outside the range of dates that excel recognizes (ie: 1860's). that's why I thought something with mid / find / len might work.

 

[bschwartz]

Try creating another column and use just the formula =TRIM(A2) <-- Assuming that your dates are in column A.

Do the spaces still exist in the new column?

 

[snb_]

Use two UDF's

Function F_date(c00)
    sn = Split(Trim(c00), "/")
    F_date = sn(1) & " " & MonthName(sn(0)) & " " & sn(2)
End Function

Function F_date1(c00)
    sn = Split(Trim(c00), "/")
    F_date1 = sn(2) & "-" & Right("00" & sn(0), 2) & "-" & Right("00" & sn(1), 2)
End Function

e.g,

if 04/05/1865 in A1

in B1: =F_date(A1)
in C1: =F_date1(A1)

 

[Rick Rothstein]

Here are formulas that should do what you want...

=TEXT(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),"00-")&TEXT(28*TRIM(LEFT(A1,FIND("/",A1)-1)),"mmm-")&RIGHT(A1,4)

=RIGHT(A1,4)&TEXT(TRIM(LEFT(A1,FIND("/",A1)-1)),"-00-")&MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)

 

[Rick Rothstein]

Use two UDF's

Function F_date(c00)
    sn = Split(Trim(c00), "/")
    F_date = sn(1) & " " & MonthName(sn(0)) & " " & sn(2)
End Function

Function F_date1(c00)
    sn = Split(Trim(c00), "/")
    F_date1 = sn(2) & "-" & Right("00" & sn(0), 2) & "-" & Right("00" & sn(1), 2)
End Function

e.g,

if 04/05/1865 in A1

in B1: =F_date(A1)
in C1: =F_date1(A1)

More than likely the OP will go with the formulas I posted in Message #8, but if UDF's were wanted, and since VB can handle a much larger date range then Excel, the functions you posted could be simplified as follows...

Function F_Date(S As String) As String
  FF_Date = Format$(Trim(S), "dd-mmm-yyyy")
End Function

Function F_Date1(S As String) As String
  FF_Date1 = Format$(Trim(S), "yyyy-mm-dd")
End Function

 

[wingfield]

Here are formulas that should do what you want...

=TEXT(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),"00-")&TEXT(28*TRIM(LEFT(A1,FIND("/",A1)-1)),"mmm-")&RIGHT(A1,4)

=RIGHT(A1,4)&TEXT(TRIM(LEFT(A1,FIND("/",A1)-1)),"-00-")&MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)

Rick,

thank you very much. I have no early idea what all that means; but it does exactly what I need it to do.

thank you to the other posters as well. I didn't try the UDF; but I'm sure it would have worked as well.

same day response is awesome and most appreciated.

best regards,

David