Text tring to date

jag108

Active Member
Joined
May 14, 2002
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi

I need to convert this text string into a standard date fromat.

each cell is formatted like "4 Mar 2007 Su" I need it to be 20070304

I have managed to seperate out the different parts i.e.

04 = day_val
"Mar" = month_val
2007 = year_val

But I am having the most difficult time getting them back to the date format mentined.

I have about 1700 entries that will need to be converted.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Assuming you need to trim the day piece off the end this formula can be copied down referring to the cell of the string (my example uses A1)

=DateValue(Left(A1,10))

Format the entire column as Custom Date

Format>Cells>Custom and enter this :

yyyymmdd

Edit:

The length of the date would change for 2 digit days so use this

=DateValue(Trim(left(A1,11))
Book1
ABCD
14 Mar 2007 Su20070304
2
Sheet1
 

jag108

Active Member
Joined
May 14, 2002
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Thank you very much Gibbs
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,083
Members
412,566
Latest member
TexasTony
Top