The easiest way is to use text to columns. The only problem is that if
you have data in the columns to the right of the text you want to split
up, you need to insert blank columns. If you need the data in the order
it is currently in then you could use formulas.
I used A1 for my names and B1 for the addresses. Using columns C - G
for my new text, in C1:G1 I input:
=LEFT($A$1,FIND(" ",$A$1)-1)
D1
=RIGHT($A$1,FIND(" ",$A$1)-2)
E1
=LEFT($B$1,FIND(" ",$B$1)-1)
F1
=IF(ISERR(MID(B1,FIND(" ",B1)+1,IF(ISERR(FIND(" ",B1,FIND(" ",B1)+1)),
FIND(" ",B1),FIND(" ",B1,FIND(" ",B1)+1))-FIND("
",B1)-1)),"",MID(B1,FIND(" ",B1)+ 1,IF(ISERR(FIND(" ",B1,FIND("
",B1)+1)),FIND(" ",B1),FIND(" ",B1,FIND(" ",B1)+1))-FIND(" ",B1)-1))
G1
=RIGHT(B1,LEN(B1)-FIND("*",SUBSTITUTE(B1,"
","*",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))
HTH
Steve
--
SteveG
------------------------------------------------------------------------
SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
View this thread:
http://www.excelforum.com/showthread...hreadid=498870