How about John van Dyke Smith Jones Sr.

or not to be as outlandish

John Van Dyke, CFO

To the OP, there is no generalized algorithm that is going to do what you
want unless you have a specific pattern or a finite set of unique patterns
that are distinct or the elements of the name are distringuishably
delimited. Usually this isn't the case for names

http://www.cpearson.com/excel/FirstLast.htm

will give some insights.

--
Regards,
Tom Ogilvy


"Jeff Standen" wrote in message
...
It helps to have a set of nice data but of course that isn't always what
happens. If you know, for instance, that the surname will always be
between
the first space and the first comma, you could do this

=MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1)

You have to play it by ear a little - you can use the IF function to
seperate different options depending on, say, whether there is a comma in
it
or not.

Jeff

"Fam via OfficeKB.com" <u18245@uwe wrote in message
news:61143504495eb@uwe...
I able to extract the first name name but what if the situation is like
John Smith, CFO
Now I canextract the first name by
=left(A1, find(" ",A1)-1)
and the title by
=trim(mid(a1,find(" ",A1),255)) but come is still coming with it and I
can
not able to resolve how to seperate the last name with the title and
extract
it out
any help please?

Fam wrote:
thanks Jeff

You could use the SEARCH worksheet function. This searches for a
specific
character in a cell and returns the position. Combine it with LEFT or
MID
[quoted text clipped - 16 lines]
Any help will be appreciated
thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1