Function similar to "LEFT"
in [Functions]
|
Prev: annuity calculation for monthly payments but quarterly compoundin
Next: Interval between now and next birthday
From: Karen D on 15 Apr 2010 16:57 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!
From: Bill Kuunders on 15 Apr 2010 17:04 Karen, Have a look at using <data> <text to columns> You van use the space as a separator. -- Greetings from New Zealand "Karen D" <KarenD(a)discussions.microsoft.com> wrote in message news:37113207-F5A3-4A6C-831E-F5AD54203D90(a)microsoft.com... > 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!
From: RagDyer on 15 Apr 2010 19:14 This won't work for everything (Jr., Sr., De La Hoya, etc.), but should work for most: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Karen D" <KarenD(a)discussions.microsoft.com> wrote in message news:37113207-F5A3-4A6C-831E-F5AD54203D90(a)microsoft.com... > 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!
From: Jacob Skaria on 16 Apr 2010 01:28
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) -- Jacob (MVP - Excel) "Karen D" wrote: > 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! |