Function similar to "LEFT"

  • Thread starter Thread starter Karen D
  • Start date Start date
K

Karen D

I'm trying to separate the last name from the first name in a field. I can
get the first name by using the "left" function. Does anyone know how to get
the last name? I tried using "right" function but on some it gives me the
whole last name plus part of the first name, others it gives me some of the
letters of the last name. Please advise - thanks!
 
Karen, Have a look at using <data> <text to columns> You van use the space
as a separator.
 
This won't work for everything (Jr., Sr., De La Hoya, etc.),
but should work for most:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
 
You should have posted few examples. I assume the firstname and last name is
separated by a space

For example to extract 'Karen' from 'Karen D' you can use

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

But if you have names without lastnames then modify the formula as below
A1 = 'Karen D'
B1=LEFT(A1 & " ",FIND(" ",A1 & " ")-1)
C1=MID(A1,LEN(B1)+2,255)
 
Back
Top