Sort column by last word in cell

Not sure I should do that as it is full of people's names and addresses - but I can explain that I have a spreadsheet in Numbers which has a column with things like 'Mr. John Smith'; or 'Mr. and Mrs. Samuel Davies' in it - so the relevant thing is the first letter of the last word in the cell, the surnames. I want to sort the whole spreadsheet, including the second column full of their addresses, according to the final word in the first column.

I've found solutions to doing it by the second word which is fine if you have a spreadsheet full of 'Tom Jones', 'Alison Brown' etc. and they all have just two names with no titles; but this is for addressing wedding invitation envelopes and the correct form has to be used for each one.

I even found a bit of code to make an AppleScript to do it, which I did; but it didn't work and I'm not surprised because it was posted in 2007 and referred to the script menu which seems to have vanished (why?) and it tells you to leave it in home/library/scripts/Numbers - and there is only a scripts folder in the hard drive library now. Others say when you make an Apple Script you can just press play and leave it to run - but how do you specify that you want it to perform the actions on a specific column of a specific document in a specific application? I tried running it but it didn't work and I even tried putting it into a home-made 'Numbers' folder in the HD library scripts folder which didn't work either.

I hope that clarifies it sufficiently without my having to divulge people's personal details.

Great to find this selection of suggestions when I got up this morning (couldn't wait around last night as I'm in the UK and it was late). Thanks!

I'm nearly there - just got to fathom out why the sort options were greyed out.

Sorry, I just went for the first one on the list. However, if people read that I had problems I doubt there will be some takers for your approach!

I actually have the extra column now which appealed to me because that is what I had been trying to do before I realised it couldn't be done with what was obviously available. However sorting them alphabetically ought to be the simple bit!

I think there is something the application thinks is wrong about those addresses which have gone red although there doesn't seem to be anything they have in common. Because of that problem, whatever it is, I think it refuses to sort the rows. It in effect is saying, "I'm not doing anything to this file while these delicate items are still unresolved on the list" and therefore the sort ascending and sort descending as well as the option to sort in the inspector are all greyed out. Either that or it's something to do with the fact that the data was imported from an Excel file. - but the problems persisted when I copied and pasted the fields into a new Numbers document.

Merge and Unmerge cells (not that I want to use them) are similarly greyed out.

Interestingly I'm just adding this edit because I've noticed that one cell where the full name was actually in red has returned black text in the newly created column!

Another edit: the new red text can be edited to black easily - it's true red text, not some kind of warning flag. But still no sort ability.

I've sorted the sort problem!

At the bottom there were some irregularly formatted cells containing people whose addresses still needed clarification. Two of the cells were vertically merged. Removing the merged cells didn't help; but when I selected the column without the queried cells at the bottom, Numbers sorted the whole lot, including the cells at the bottom.

I think Apple really needs to give us this functionality, however. The problem with doing this from a script is that it's a one-off operation. I put a 'z' in front of the last name of the queried items to keep them at the bottom and then realised they were not going to move unless I ran the script again with another column. Essentially those cells have been worked out and filled with ordinary text rather than having a self-updating formula in them. It surely wouldn't be difficult? They already have that kind of functionality in Contacts and Address Book.

Perhaps I'll give yours a go if I have to do it again, YellowBox!

Took me a while to work out what was actually happening and how to make it work on longer names (i.e. families with more than one surname included) but I got there! And it updates - I added 'z' to the front of all the queried names and they all plunged to the bottom of the list

Thank you so much, all of you! My Excel friend will be so impressed I managed to do this with shiny, yet probably in his eyes, puny Apple software (actually he probably won't and I'd be more impressed if it were selectable as an option in the software itself).

Anyway, bedtime in the UK again. Nighty night and thank you again!

… and why some of the names have gone red in the new column?