|
Enter the following name in cell A1: George W. Bush
Use the following function to extract the first name:
=LEFT(A1,FIND(" ",A1)-1)
The Result: George
Use the following function to extract the middle name:
=IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)), FIND("
",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+
1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND("
",A1)-1))
The result: W.
Use the following function to extract the last name:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))
The result: Bush
|
Cool
Don Cherry wrote on December 31, 1969 19:00 EST |
| The only thing missing is for the rare case when there are two words in someone's last name, e.g. "Art X Van Delay" the last name is "Van Delay" but you only wind up with "Delay." |
Lucky Roulette
John wrote on December 31, 1969 19:00 EST |
| Greetings to all! Excuse for this message, but at you excellent design of a site! Very much it was pleasant to me, I shall come here very often! |
How to combine?
KY wrote on December 31, 1969 19:00 EST |
After the words are separated, how do you combine Column A "George" with Column B "Bush" and come up with Column C "Bush, George"?
Thanks, much!
K, from Manila |
saved me
Mike wrote on December 31, 1969 19:00 EST |
I had a time sensitive document that I had to use this function and this saved me.
Thanks a ton |
|
Learn MS Excel 2002 VBA/XML Programming
