» 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:
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Flipping Properties: Generate Instant Cash Profits in Real Estate
- Financial Reporting and Analysis (2nd Edition)
- How to Use Financial Statements: A Guide to Understanding the Numbers
- Final Accounting: Ambition, Greed and the Fall of Arthur Andersen
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
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
