Ask for Support: Excel Tip for Separating Full Name into First Name Last Name

Microsoft Excel is that program we love and love to hate.

Exceltip.com is a good resource for tips on doing the craziest, and most useful, things in Excel.

Excel Tip: Separating first name and last name using formulas in Microsoft Excel

Let’s say you have a grid of subscribers. The first column (column A) contains the Full Name, “John Smith”, but now you need to sort by last name. How do you do it?

Cell A1 contains a name: John Smith

1. Create a new, blank column.

2. Make sure the format of the column is “general”.

3. In the new, blank column, in row 1, type this formula:

The formula for extracting the first name is: =Left(A1,Find(” “,A1))
The formula for extracting the last name is: =Mid(A1,Find(” “,A1)+1,Len(A1))

4. If the formula works, copy and paste the formula in the whole column.

Now, you want to make the new column contain the value rather than the formula.

5. Select your new column. Copy > Paste Special. Select Value. Save.

Now you can sort the column containing Last Name.

Note: Paste Special is different than Paste. Paste Special lets you define what part of the info you want pasted into the column. In this case you want the value of the formula, “Smith”, rather than the formula “=Mid(A1,Find(” “,A1)+1,Len(A1))”.

Is some piece of technology driving you mad? Are you a non-techie in need of support? Email me. .(JavaScript must be enabled to view this email address)
I will answer your questions in upcoming newsletters.