Spreadsheet sort by last letter?

November 21, 2006 7:11 AM

Is it possible to sort a spreadsheet alphabetically by last letter or by retrograde spelling? Excel, StatView, etc.?

I need to preserve the relationships between 2 columns, but I need to sort by last letter. Are there any other solutions out there?

posted by imposster to computers & internet (6 comments total)

There may be an easier way to do this, but this is the way I do it:

You have two columns A1 and B1. You want to sort by the last letter in A1

Strip out the last letter in each cell, in the column you want to sort by. How you do this is, get the LEN of the cell.

=LEN(A1)

this value goes in Column C

then use the MID function to get the last letter


MID takes three values (text, start_number, number_chars)

so you want =MID(A1,C1,1)

This will take the value in A1, start from the length of the string, and repeat only one character from it, namely the last one).

You might then need to copy the resultant column into notepad, then paste it back into column C. this way, the values will be static for the data you use. Make sure the values all line up, though.


You then highlight Column C click sort (the AZ button), it will ask you do you want to expand the selection, say yes, et voila!
posted by snailer at 7:19 AM on November 21, 2006

Sorting by just the last character would be fairly simple - if the spreadsheet offers a RIGHT formula, you can use it to extract the last character of the text into a hidden field and sort the sheet on that field. If it doesn't, whatever its LENGTH equivalent plus its SUBSTRING function can be used, as well in something like =SUBSTRING ( A1, LENGTH(A1), 1).
posted by jacquilynne at 7:20 AM on November 21, 2006

Taking snaller's method and improving it slightly:

If the text you want to sort by is in C2, the formula =CONCATENATE(MID(C2,LEN(C2),1),MID(C2,LEN(C2)-1,1),MID(C2,LEN(C2)-2,1),MID(C2,LEN(C2)-3,1)) will take the last four letters and reverse them. (So "Smith" would turn into "htim".) Put those in a column and sort by that column. (There's no need to copy into Wordpad and paste back by text; it will sort by the results of a formula fine.)

Four letters is arbitrary; you can put in as many MID statements as you need to avoid duplication.
posted by raf at 7:30 AM on November 21, 2006

Oh, unfortunately you can't have more instances of MID in your formula than you have characters in the shortest entry: if you try to apply the above formula to "Win", you get #VALUE because taking the -1st value of that entry is undefined.
posted by raf at 7:32 AM on November 21, 2006

Thanks for all of the great answers. Snailer's method worked like a charm. I'll have to try raf's version a little later. I never had any idea there were all of these great commands available for working with text in a spreadsheet.
posted by imposster at 7:41 AM on November 21, 2006

i'm a little late to the game, but if you just need to sort by last character in excel, this works:

=right(A1, 1) will give you the last letter in cell A1. then just sort by the column with the formulas in it. Also, you don't need to paste to notepad because a) as raf said, it will sort the results of a formula and b) if you just wanted to have the formula results, select the column, ctrl-C to copy and then right click the column and select "paste special" and then in the window select "values" and it will paste the values (results of the formulas) where the formulas were.
posted by snofoam at 1:08 PM on November 21, 2006