split one cell into two excel

[lisat76]

I have a spreadsheet that has several cells each of which have 2 dates
like
January 1 2001 to February 10 2001
March 2 2005 to April 2 2005

I would like to split this into 2 cells where one cell has the first
date and another has the second date.
I guess i want to do a kind of reverse concatenation.
I thought maybe I could base it on the word "to" to split it before the "to" and after the "to". The word "to" is actually in each cell

 

[mintjulep]

If you only need to do this once to clean up the spreadsheet, the easiest thing would be Data | Text to columns...

If you really need a formula-based approach, a combination of LEFT, RIGHT and SEARCH will get you there. They are all documented in Excel's help.

 

[Gavona]

You can do it by formula by using Find to identify where in the string the word "to" occurs. I have used the Trim function as well so that it will cope if users have input no spaces or extra spaces around the "to":

With the original text in Cell A2:
=TRIM(LEFT(A2,FIND("to",A2)-1))
=TRIM(MID(A2,FIND("to",A2)+2,99))

Note with the above technique the results are TEXT and not proper excel dates (see FAQ for this forum).
To convert to a proper date I would need to manipulate each of the results using the above techniques to produce text in the format 1 January 2004. However this depends on your Regional Settings for how the date should display. You may be lucky so try =datevalue(TRIM(LEFT(A2,FIND("to",A2)-1)))

IF you are able to use Text to Columns then make sure you specify that the columns are dates in the format MDY. However I suspect that Text to columns will be difficult as you may need to specify that columns are delimited by a space (thus getting month, day and year in separate cells). You would then need to re-assemble using somethging like =datevalue(concatenate(.....))

I hope these notes help point you in the right direction,


Gavin

 

[lisat76]

thanks I will give it a shot