Cell Value

Posted by neatley on February 13, 2002 8:40 AM

Hi All

I have a column of names (ie Mr A Smith, Mr Alan John Smith) and I need to extract just the surname in to a new column. I know I can do text to columns but that doesn't put the surname in the same column. Is there a formula to do this?

Thanks for any help.

Posted by Mark W. on February 13, 2002 9:17 AM

You could use the array formula...

{=MID(A1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1))),1)+1,LEN(A1))}

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Aladin Akyurek on February 13, 2002 9:49 AM

=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

where A1 houses the first full name from which to extract the surname.

Caveat. It does not handle cases of J. Doe, Jr.

=======

Posted by Mark W. on February 13, 2002 10:40 AM

Very nice! ...about your caveat...

Comma delimited titles and the like can easily
be stripped off using Data | Text to Columns...
on either the original name list (column A) as
a pre-conditioning step or on the column
containing the results of your formula.

Posted by neatley on February 15, 2002 2:48 AM

{=MID(A1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1))),1)+1,LEN(A1))} Note: Array formulas must be entered using the

Thanks for all your help, I shall be using this alot!

Neatley