Extracting middle names and last names

[mahmed1]

Hi,

I have seen many different ways to extract middle names and last names. Some are really confusing to figure out.

Some people use, find and others use search to find a pattern occurring.

What is the best and easiest way/formula to use to extract middle and last names. I guess the first name is pretty easy as everyone seems to extract the first name using the same formula. What if there is 1,2 or more middle names...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
i,e <o></o>
<o> </o>
STEVEN JOHN PAUL GERRARD
MICHAEL JOHN H ANTHONY SMITH
SHAUN H NORMAN
ROGER CLARK

What is the best method/easiest formula to extract any middle names and last names. Can you please give examples of different formulas. <o></o>
<o> </o>
What's the difference between the search and find?

gracias <o></o>
<o> </o>

 

[István Hirsch]

Another version:

B1:

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

C1:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1&"ß",B1&" ","",1),D1&"ß",""))

D1:

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

 

[maryinred]

cool! Thanks again!!

 

[hiker95]

maryinred,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.