asked on 9/11/2013

Pull last name out of cell

In column A I names. Some are first and last, some are first, middle and last. In Column B I want just the last name. Somehow tell Column be to take out everything up to the space bar on the left of the last name. Any way to make this happen?

Microsoft Excel

Last Comment

barry houdini

9/12/2013

Alan

9/12/2013

Hi,

If the full names are in column A, then paste in this formula to another column and copy down:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))

I think I got that from someone else, so I should be crediting someone - I apologise for not recalling who!

Alan.

Edit: Found the source: Jean-François Corbett posting in StackOverflow

<<Link to competing Q&A web site omitted. Name of poster on that site added.--byundt, Microsoft Excel Topic Advisor>>

ASKER CERTIFIED SOLUTION

barry houdini

9/12/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

Followers

Top Experts

Get a personalized solution from industry experts

Ask the experts

"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23
"Your help has saved me hundreds of hours of internet surfing."
@fblack61
"Just a dang good service!"
@golferski
"Worth every penny."
Steve Hougom
"It’s been a life saver."
Maria Halt
"Best support site I’ve seen!"
Bob Schneider
"I would be lost without them."
@fblack61
"Saved my job multiple times."
Walt Forbes
"I love this site."
Maria Duwe
"Friendly respectful help."
Andrew Kowtalo
"Such top-notch experts."
@magento
"The best money I have ever spent."
@rwheeler23
"Beyond any comprehensible value"
George Morris
"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23

Read over 600 more reviews

TRUSTED BY