Help: How to extract date text from cell

stupidguy

New Member
Joined
Apr 3, 2009
Messages
3
I have a column of dates in text that aren't in a proper format and can't be date formatted. The text in the cells look like this (note the commas and spaces):

Friday, December 14, 2007

I'd appreciate help getting this non-uniform text date into a MM/DD/YYYY format:

12/14/2007

Thanks!!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
click on cell go to format cell on the number tab go to custom and type mm/dd/yyyy
 
Upvote 0
I have a column of dates in text that aren't in a proper format and can't be date formatted. The text in the cells look like this (note the commas and spaces):

Friday, December 14, 2007

I'd appreciate help getting this non-uniform text date into a MM/DD/YYYY format:

12/14/2007

Thanks!!

Try....
=TEXT(TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))+0,"mm/dd/yyyy")

HTH
 
Upvote 0
Hi,

I messing about with the formulas and couldnt get either to work. Not sure if this is because of geographical settings. I put together my own one which works on my pc.

=(LEFT(RIGHT(A1,8),2)&"-"&MID(A1,FIND(",",A1)+2,3)&"-"&RIGHT(A1,4))*1

This makes the cell into an actual date, and you can format it respectively.
Book1
ABCD
1Friday, December 14, 200714/12/2007#VALUE!#VALUE!
2Saturday, December 15, 200715/12/2007#VALUE!#VALUE!
3Friday, May 01, 200901/05/2009#VALUE!#VALUE!
Sheet1


Hope it helps
 
Upvote 0
Hi,

I messing about with the formulas and couldnt get either to work. Not sure if this is because of geographical settings. I put together my own one which works on my pc.

=(LEFT(RIGHT(A1,8),2)&"-"&MID(A1,FIND(",",A1)+2,3)&"-"&RIGHT(A1,4))*1

This makes the cell into an actual date, and you can format it respectively.

This worked out really well too!!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top