On Tue, 3 Jan 2006 21:19:02 -0800, "zomex"
wrote:

eg. "mick jagger bush" "muhammad ali clay" "ayesha bin batool"
suppose that these r the names and i want formula that would be generic to
all, such that it gets "jagger" "ali" and "bin"


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

Will find the first word after the first space.

If there may be multiple spaces within the string, then replace all A1 with
TRIM(A1)

On the other hand, a little more complex, imagine that you may have name
strings with 2, 3, or more names.

You might want to extract the first name, last name, and all of the middle
names separately. You also might want to be able to strip off Titles and such
(e.g. Mr., Ms., Jr, III, Sr.)

If this is a possibility, then regular expressions would be a more powerful
tool to use. You can look at that by installing Longre's free morefunc.xll
add-in from http://xcell05.free.fr

First Name: =REGEX.MID(TRIM(A1),"\w+")
Last Name: =REGEX.MID(TRIM(A1),"\w+$")
Middle Names: =REGEX.MID(TRIM(A1),"(?<=\s)(\w+\s)+")

Note that the formula for Middle Names will return a blank if there are only
two names (i.e. no middle name).


--ron