» Extract the first, middle and last name in Microsoft Excel
CATEGORY - Excel Text , Excel Formula
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- Financial Peace: Revisited
- The Basics of Finance: Financial Tools for Non Financial Managers
- Financial Risk Manager Handbook, Second Edition
- Infectious Greed: How Deceit and Risk Corrupted the Financial Markets
Cool
Don Cherry
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
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
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
I had a time sensitive document that I had to use this function and this saved me.
Thanks a ton
