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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
click on cell go to format cell on the number tab go to custom and type mm/dd/yyyy
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=REPLACE(A1,1,FIND(",",A1)+1,"")+0

Format as date.
 

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864

ADVERTISEMENT

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
 

stupidguy

New Member
Joined
Apr 3, 2009
Messages
3
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!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,519
Messages
5,832,230
Members
430,117
Latest member
RRattle

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
Top