Splitting Text String into 2 Cells

[nscobie]

Hi.

I am really hoping someone can help me with this one. I've used a Concatenate function before to join first and last names together into one cell, now I need to do the opposite.

Wherever there is a space used within a cell, I want the program to recognise it and move the next word into a separate cell. Most values I have are First and Last Name ie. John Smith. But some are initials and names ie. J M Smith.

Can someone please tell me how to divide text strings?

Thanks in advance

 

[Aladin Akyurek]

On 2002-03-13 09:05, nscobie wrote:
Hi.

I am really hoping someone can help me with this one. I've used a Concatenate function before to join first and last names together into one cell, now I need to do the opposite.

Wherever there is a space used within a cell, I want the program to recognise it and move the next word into a separate cell. Most values I have are First and Last Name ie. John Smith. But some are initials and names ie. J M Smith.

Can someone please tell me how to divide text strings?

Thanks in advance

In B2 enter:

=SUBSTITUTE(A2,C2,"")

In C2 enter:

=RIGHT(A2,LEN(A2)-SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Aladin