Separating text with various number of characters.

B

Bonnie

We have a spreadsheet with first names and last names in
the same column. We need to put last names first. If we
use the left or right function, we cannot copy it down
because each row would have a different number of
characters to be separated. Is there a way to indicate
this in a function.

Thanks,

Bonnie
 
F

Frank Kabel

Hi
use
=LEFT(A1,FIND(" ",A1)-1)

and
=MID(A1,FIND(" ",A1)+1,255)

or use 'Data - Text to columns'
 
J

Jason Morin

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-
1)

Of course you'll run into issues with first and/or last
names with multiple words like Oscar de La Hoya and Jo
Ann Butterfield.

HTH
Jason
Atlanta, GA
 
Top