How do I sort data by surname on excel? I have office 2007

Can you give or post a an example of how you'd like that to work? Without any formulas, just create some cells that show some sample data, then show how you want it to look after.

Then I'll have an idea.

Also, is this a one-time thing you want to accomplish, or something that has to keep sorting over and over again because the list keeps changing?

You can watch this video and it will show you a cool manual way to do it, but it's not automated.
Sort A List By Last Name
(be sure to start the video in the center of the page)

If you have a table of names, You can highlight the table and select the Sort option on the ribbon. You can then choose to sort on selected columns in sequence.

Another manual way (based on an answer I've given elsewhere on a similar topic):

1. Insert a blank column to the right of your name column. Let's assume the names are currently in the A column.
2. In the new empty cell at B1, enter this formula:
   
   Quote:

   ---

   =RIGHT(A1,LEN(A1)-FIND("<^>",SUBSTITUTE(A1," ","<^>",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1))

   ---

3. The last name from cell A1 should appear
4. Copy the formula down and the last names should appear.
5. Sort the sheet by this new column

Quote:

---

Originally Posted by ScottGem

If you have a table of names, You can highlight the table and select the Sort option on the ribbon. You can then choose to sort on selected columns in sequence.

---

Scott, I believe his problem is the First/Middle/Last names are all in ONE cell. That's why he needs a formula like the one I suggested and a "helper column" to accomplish it.

Nice to speak to you again JB. Thanks Scott for your answer but I think JB knows what I am saying. I have a list of names in column A it includes the first name and surname. However I want to sort by the surname so I think JB's answer is correct. I will try shortly.

I have done it however a lot of spaces have been left, it only seems to work for some.

Post some examples of the data in the cells it fails for. You could even email me the sheet, if you're a trusting sort. I think I've earned my stripes, you know?

Quote:

---

Originally Posted by JBeaucaire

Scott, I believe his problem is the First/Middle/Last names are all in ONE cell. That's why he needs a formula like the one I suggested and a "helper column" to accomplish it.

---

Ahh, well that needed to be spelled out. I generally use 5 columns (or database fields) for person names:

Salutation (Mr, Ms, etc.)
First
MI
Last
Suffix (Jr, Sr, etc.)

It's a lot easier to concatenate fields together than break them apart.

Quote:

---

Originally Posted by ScottGem

Its a lot easier to concatenate fields together than break them apart.

---

Agreed.