MrExcel Publishing
Your One Stop for Excel Tips & Solutions

separate two words from a cell in two cells

Posted by ovidiu chise on December 27, 2001 2:40 PM


please help me

i desperate need to know how can i split, from example,

a cell with the words "name address" in a cell with "name" and a cell with "address", i mean, in two columns.

thank you very very much

Posted by Cory on December 27, 2001 2:55 PM

If "name address" is in cell A1, in cell B1 type this formula : =MID(A1,1,SEARCH(" ",A1))

Then in cell C1 type this formula: =MID(A1,SEARCH(" ",A1)+1,20)

That should do it!

Posted by Joe Was on December 27, 2001 2:58 PM

Tom Swift =RIGHT(A1,LEN(A1)-FIND(" ",A1)) =LEFT(A1,FIND(" ",A1)) By find space.
Swift Tom =RIGHT(A2,LEN(A2)-FIND(" ",A2)) =LEFT(A2,FIND(" ",A2)) By find space.
Peters, Willy =RIGHT(A3,LEN(A3)-FIND(",",A3)-1) =LEFT(A3,FIND(",",A3)-1) By find comma.
Willy, Peters =RIGHT(A4,LEN(A4)-FIND(",",A4)-1) =LEFT(A4,FIND(",",A4)-1) By find comma.

The names to the right of the formulas are in column "A." The formulas are each in a column, side by side. The description at the end tells what the formula pair does.

I would copy the tests in column A and the first formula in column B and the next formula in column C and the description in column D. Do this for all four sets. All on a test sheet. To see which you should use or want. JSW

Posted by Derek on December 27, 2001 4:19 PM

If you do not want to use a formula you can split text into columns by clicking on Data in your toolbar and selecting Text-to-Columns, and follow the instructions it provides.