Originally Posted by
farman8
JB: thank you so much. That was easy. But some of the ppl in the list have middle names or middle intials. there are around 450 of them. any way to fix that?
What do you mean "fix" it? The TEXT TO COLUMNS thing put all of those into cells of there own.
Is what you really mean:
Ok, then you need some more blank columns. If names are in ColumnA starting at A1, enter these formulas:
B1:
=LEFT(A1,FIND(" ",A1)-1)
C1:
=IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)), FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+ 1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))
D1:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Copy them down the WHOLE 1000 rows of data.
Then highlight the data
Click Edit > Paste Special > Values (this removes the formulas)
Delete column A.
Done.
(resource)
Bookmarks