Split Date and Text

Hi, i have about 300 cells in column A that contain data like:

KILMARNOCK SEPTEMBER 26/9/05
WINKINGTON OCTOBER 3/10/05

Theres no commas to seperate them, but i need to keep the name, eg Kilmarnock in cell A. And put the date into cell B. The name of the month is not necessary to keep.

Help, im stumped!!

Have you tried the data menu->text to columns and then choosing the space character as the delimiter ?

I have but as the space in between the words and date differs for almost every cell, its impossible to set a line to split them, if you see what i mean.

You shouldn’t need to set a line. Choose the “Delimited” option (not “Fixed Width”) and then under “Delimiters” tick the “space” box.

If you can’t make that work you could consider using formulas to extract the data you need.

If the text is in cell A1 use this formula to extract the name

=LEFT(A1,FIND(" ",A1)-1)

and for the date

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

Hello,

If your format of words does not change in every cell, below formula would work.

B1: =LEFT(A1,FIND(" ",A1)-1) and on C1: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))). Select B1:C1 and copy the formula for the rest of the rows.

If what you meant by the space in between the words and date differs from every cell like this --> KILMAR[space]NOCK[space]SEPTEMEBER[space]26/09/2005. We have to do different approach.

Try to copy this text on A5, KILMAR NOCK SEPTEMBER 26/9/05, on B5: =LEFT(A5,FIND(" ",A5)-1)&IF(ISERR(MID(A5,FIND(" ",A5)+1,IF(ISERR(FIND(" ",A5,FIND(" ",A5)+1)), FIND(" ",A5),FIND(" ",A5,FIND(" ",A5)+1))-FIND(" ",A5)-1)),"",MID(A5,FIND(" ",A5)+ 1,IF(ISERR(FIND(" ",A5,FIND(" ",A5)+1)),FIND(" ",A5),FIND(" ",A5,FIND(" ",A5)+1))-FIND(" ",A5)-1))

on c5: just copy the formula from c1 and change the cell references to c5.

Regards,

Corine

Corine

Wow. Thats some serious formula! I'm sorted now guys, cheers!