How to sort a column based on the last word in each row of that column

Topic

New Reply

I have a column (A) of 1000 rows of text fragments (one cell per row), and wish to order them according to the last word in each cell.

If there is more than one word in a cell, the words are space-delimited, with no end punctuation. (Most of these end words are state postal abbreviations, or spelled-out countries.)

One solution would be to extract the last word and write it to the next column (B), then sort the entire array using B as the key.

What would be a formula or method to accomplish the building of column B?

Is there a more efficient way, not requiring the additional column (B)?

See the example1.xls file attached here.

Reply | Quote

Replies

Hi cosmlou

I think you need to use column B as a ‘helper’ column for what you want.
But you can always delete that after the sort has been done.

In my attached file, I have added sample test data to make 1000 rows (to check the solution is reasonably efficient).
I have added vba code to do the required ‘sort-columnA-by-last-word’
I have added a short-cut-keystroke to execute this sort macro:
Ctrl-shift-Z will run the macro.

This uses a function to get the ‘last word’ of the cell:

Code:

Function lastWord(z As String)
zArr = Split(Trim(z), " ")
lastWord = zArr(UBound(zArr))
End Function

The routine to do the sort is:

Code:

Sub sortColALastWord()

zLastRow = Cells(Rows.Count, "A").End(xlUp).Row

temp = "b1:b" & zLastRow
[b1] = "=lastWord(a1)"

[b1].Copy Range(temp)
Range(temp) = Range(temp).Value
[b1].CurrentRegion.Sort key1:=[b1]
Range(temp).Clear

End Sub

It seems to work very fast on my laptop.
There are always other ways of doing this of course!

zeddy
‘

Reply | Quote

Many thanks for the prompt response. -Collier

Reply | Quote

Cosmlou,

Using your sample spreadsheet, to find the last word, in cell B1 place the following formula then copy down

In cell B1 then copy down

Code:

=TRIM(RIGHT(SUBSTITUTE(A1,” “,REPT(” “,255)),255))

Name the range of values in column B, “List” (without the quotes)

To sort the named range “List”, place the following formula in C1

In cell C1:

Code:

=INDEX(List, MATCH(SMALL(COUNTIF(List, “<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

Make the formula an array formula by placing the cursor anywhere in the formula then pressing Ctrl+Shift+Enter then release all keys. The formula in C1 will look like this:

Code:

{=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))}

Copy down and your last words will be sorted. Hide column B if you like. Source of formulas found here

http://www.mrexcel.com/forum/excel-questions/706066-simple-formula-sort-text.html#post3486280
http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/

HTH,
Maud

Reply | Quote