Microsoft Excel - reformat text to a date - Asked By Cherifa Hima on 30-Jul-13 01:24 PM

Hi,

I need an excel formula that picks the date from the text and split it into two dates: start and end date in two different cells. e.g.

August 19 - November 25, 2013 will become 08/19/2013  in one cell and 11/25/2013 in another cell.

Hello Cherifa,
Assuming your text is in A6, the following two formulae will do it.

=DATEVALUE(MID(MID(MID(SUBSTITUTE(A6," ","^",1),1,256),FIND("^",SUBSTITUTE(A6," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",1),1,256),FIND("^",SUBSTITUTE(A6," ","^",1)),256))-2)&"/"&MONTH(DATEVALUE(LEFT(A6,FIND(" ",A6)-1)&" 1"))&"/"&MID(SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))),FIND("^",SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))))+1,256))

=DATEVALUE(LEFT(RIGHT(A6,8),2)&"/"&MONTH(DATEVALUE(MID(MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256))-2)&" 1"))&"/"&MID(SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))),FIND("^",SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))))+1,256))

You might have to switch the day and month extraction bits around depending on the set up of your computer date system and you will have to format the cells to get the date in your preferred format.

Regards
Harry

Thanks. The second formula is giving me #VALUE! and the first one does not work for all cases ( see examples below). I cannot change the extraction of the date format. It is like that in the marketing database.

July 11 - December 22, 2013

July 20 - August 31, 2013

November 1 - December 20, 2013

September 1 - October 31, 2013

July 27 - October 31, 2013

November 2 - December 20, 2013

August 16 - September 30, 2013

January 4 - January 31, 2014

November 1 - December 20, 2013

September 1 - October 31, 2013

September 8 - September 30, 2013

August 24 - September 7, 2013

November 1 - December 18, 2013

July 28 - October 31, 2013

July 1 - December 20, 2013

August 20 - October 31, 2013

Hello Cherifa,

cherifa1.zip

Your data works absolutely fine.  Perhaps there was an error in copying the formulae or you need to switch the day and month sections of both if you are using a computer set for american format dates.
Regards
Harry

Thanks.
 I am using excel 2010. When I open your file I see that the formula is working but when I enable editing I see #value! in most of the cells.

Hello Cherifa,
Here is a file from 2010 with the formulae in both date formats.  Hopefully one of them will be OK for you.
cherifa2.zip
Regards
Harry

Thanks it is wroking now.