Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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!

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.

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

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.

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.