i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help

--
aprilshowers

Hi,

=TRIM(RIGHT(A1,FIND(" ",A1)+2))

if this helps please click yes thanks

"april" wrote:

i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help

--
aprilshowers

Here's one way that Biff posted about a year ago:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

with your text in A1.

Hope this helps.

Pete

On Dec 21, 5:21*pm, april wrote:
i have many (over 8000) cells with names and i want to isolate the last name.
*the problem is that the format varies. *Possible formats

1. *A. Sharp
2. *Allison Sharp
3. *Dr. Allison Sharp
4. *Dr and Mr Allison sharp
5. *Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. *i have tried
text to columns but because there is not pattern this isn't very efficient.

Try the below in cell B1 with data in cell A1 and copy down as required. The
below formula would split the cells with comma to pick the first element and
then extract the last word.

=TRIM(RIGHT(SUBSTITUTE(IF(ISNUMBER(FIND(",",A1)),
REPLACE(A1,FIND(",",A1),99,""),A1)," ",REPT(" ",255)),255))

--
Jacob


"april" wrote:

i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help

--
aprilshowers

On Mon, 21 Dec 2009 09:21:01 -0800, april
wrote:

i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help

From what you post, it appears that the last name is either the last word in
the string, or it is the first word that is followed by a comma.

That being the case:

=TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A1)&",",
FIND(",",TRIM(A1)&",")-1)," ",REPT(" ",99)),99))

--ron

Unfortunately none of these solutions work. Jacob and Pete, your formulas
give me the last character of the string. for instance, if the string was
Dr. Tom Feelgood M.D., your formulas returned "."

Ron, i was mistaken in my examples. there is no "," in the string. i
believe that i gave an example of Capt. John Smith, USN, Ret. Instead the
string reads Capt. John Smith USN (ret).

thanks for the help though. any more ideas?

thanks in advance
--
aprilshowers


"Eduardo" wrote:

Hi,

=TRIM(RIGHT(A1,FIND(" ",A1)+2))

if this helps please click yes thanks

"april" wrote:

i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help

--
aprilshowers