Sorting by last name if entire name in single column

**lyambor** · February 26th, 2005

This might have been asked before but could not find it when I searched and I imagine others might try to do it

If I have a name column (data imported from a main frame)
how can I sort by last name if the entire name is entered into the column such as

Column A contains----------------------------
Marsha C. Browning
Adam Martin Jr
Peter J. Jennings
Cindy S. Tate
John Paul Jones Jr.

Some names include Middle Initial, some might not, some have Jr

small sample attached

MAny thanks

***Bill Rockenbach*** · February 26th, 2005

This is an age old problem. The only way I know of to sort by last name is to first separate out the last name into a separate column then sort on that column.

For the sample list you provide:
1.Select the range that has the full name.

2.Then goto menu Data > Text to Column and follow the wizard. Use space as the delimiter. With your sample this will give you 4 columns. Column B = First name, Column C = mixed Initial and Last Name, Column D = Mixed Last Name and "Jr" and Column E = "Jr", "II".

3.In Column F enter the formula:

= TRIM(IF(LEN(C2)=1,D2 & " " & E2,C2 & " " & D2))

I have found there are always exceptions that have to be manually adjusted. This is why it is always best to use separate fields in data bases for first middle and last names.

Hope this helps.

Bill

**lyambor** · February 26th, 2005

Many thanks - I will try it out asap -
I was sure this was a common problem and since the data is coming from an old main frame system I have no control how the fields are coming in

I appreciate your time

**ColinHeico** · February 28th, 2011

Today I came up with a way to sort the last name out of a list of combined names that I thought I'd share.

1. Copy your full name column to the last column
2. Under data, select text-to-columns. Choose delimited and use space as the delimiter.
3. add headings to the new columns that step 2 created. I personally use #1, #2, #3, #4 for as many as needed
4. add another column heading after these titled LastName
5. Under data choose sort. Sort by #4 then #3, then #2 then #1
6. highlight the first row in LastName and press =, then highlight the last name in #4 (or whatever your last column is) and press enter
7. copy this "formula" and paste it to all the rows below it where there is a last name in column #4
8. highlight the first empty row in LastName and press =, then highlight the last name in #3 and press enter
9. copy this "formula" and paste it to all the rows below it where there is a last name in column #3
10. repeat where there are last names in column #2 and again for column #1
11. Sort the list by your new LastName column.

Now this is a dumb sort that will leave you with some Jr.'s or III's as last names that will need corrected. This should be quick enough since they are now grouped together alphabetically.

Compared to either typing all the last names by hand or the slew of equally dumb yet very complicated formulas suggested across the internet (which rely on assumptions that are often not true, like everyone has a middle initial) I feel this wins hands down. Once you understand how it works, this is far simpler to use then creating looped if conditions with length and right statements.

American Life

Wednesday, September 26, 2012

Sorting by last name if entire name in single column - sort last name excel 2007

Thread: Sorting by last name if entire name in single column

Thread Tools

Search Thread

Sorting by last name if entire name in single column

Re: Sorting by last name if entire name in single column

Re: Sorting by last name if entire name in single column

Re: Sorting by last name if entire name in single column

No comments:

Post a Comment

About Me

Evil Israel, Jews