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!!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

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