Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

separate two words from a cell in two cells

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

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


Check out our Excel Resources

Re: separate two words from a cell in two cells

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!


Re: separate two words from a cell in two cells

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



Re: separate two words from a cell in two cells

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.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.