Separating text with various number of characters.

  • Thread starter Thread starter Bonnie
  • Start date Start date
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
 
Hi
use
=LEFT(A1,FIND(" ",A1)-1)

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

or use 'Data - Text to columns'
 
=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
 
Back
Top