Splitting Cells

[Guest]

Can anyone tell me how I can split items in a cell which are seperated by a space? i.e John Smith. I want John in one cell Smith in the other. at the moment they are in one cell.

Thanks

 

[Guest]

thats pretty simple.. All you have to do is nothing but select the cell and go to Data menu. Here, click on the option "Text to Column". Proceed with the wizard by selecting Delimeters. And, specify empty space as the delimeter in the wizard. This will let you split the cell as you require.

Have a nice day!

Krish.

 

[Ivan F Moala]

On 2002-03-15 02:44, Anonymous wrote:
Can anyone tell me how I can split items in a cell which are seperated by a space? i.e John Smith. I want John in one cell Smith in the other. at the moment they are in one cell.

Thanks

Assuming name in A1 then in B1 formula =
=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Gets the Last Name

In C1 for 1st Name =
=LEFT(A1,LEN(A1)-LEN(B1)-1)

Ivan

 

[Aladin Akyurek]

On 2002-03-15 02:44, Anonymous wrote:
Can anyone tell me how I can split items in a cell which are seperated by a space? i.e John Smith. I want John in one cell Smith in the other. at the moment they are in one cell.

Thanks

In B2 enter:

=SUBSTITUTE(A2,C2,"")

In C2 enter:

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

where A2 houses a target name.

Or use Data|Text to Columns
This message was edited by Aladin Akyurek on 2002-03-15 03:06

 

[Guest]

thanks Krish. Simple really to you. But a time saver for me. Thanks again.

 

[Dave Hawley]

To get even shorter you can use:

=REPLACE(A1,FIND(" ",A1),LEN(A1),"") In cell B1

=TRIM(SUBSTITUTE(A1,B1,"")) In cell C1

This assumes the name is in cell A1