# Excel 2010 : extract month day year from text

#### wingfield

##### New Member
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
To convert the text to date

=DATEVALUE(TRIM(A1))

and format as Date.

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

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.

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?

Use two UDF's

Code:
``````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)

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)

Use two UDF's

Code:
``````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...
Code:
``````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``````

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

Replies
16
Views
563
Replies
4
Views
198
Replies
4
Views
128
Replies
1
Views
172
Replies
12
Views
359

1,203,690
Messages
6,056,755
Members
444,889
Latest member
ibbara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back