split column

  • Thread starter Thread starter tlee
  • Start date Start date
T

tlee

I have a column of data in the following format
column A
John B. Smith

What I want to do is split this column into three separate column so that it
will appear as below
Column B Column C Column D
John B. Smith.

Is there a formula that can help me do this.


TIA
 
Tlee,

If all the names follow the same format (you don't say), you can use Data -
Text to columns, using a space as the separator. Make sure that the columns
to the right are empty before you start.
 
What you might try to do, is to create "helper" columns, which would contain
text formulas, with which you could separate the first, middle (if any), and
last names into individual columns.
You would then remove the text formulas, leaving just the data behind.

Say your names are in column A.

Enter this formula in B1:
=LEFT(A1,FIND(" ",A1)-1)

Enter this formula in C1:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND("
",A1)+1,LEN(A1)-(LEN(B1)+LEN(D1)+2)),"")

And enter this formula in D1:
=RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"
","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

In the above formula, watch out for "word wrap".
There is a <space> between the quotes (" ") in both Substitute portions of
the formula.

Now, select B1:D1, and drag down to copy as needed.

After you have all your names separated, select columns B, C, and D.
Right click in the selection, and choose "Copy".
Right click *again*, and choose "PasteSpecial".
Click on "Values", then click <OK>, then <Esc>, and you should now have
*only* your data, which you can then copy to anywhere you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Unfortunately they do not. Some have the middle name and some don't.
 
Back
Top