Pull last name out of cell

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-12
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?
Question by:cansevin
LVL 22

Expert Comment

ID: 39485425

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


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


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>>
LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39486298
Try this formula

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

regards, barry

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question