We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How do I sort in Calc by the last word of a field? [closed]

asked 2014-10-08 18:46:20 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I Have a spread sheet that has a list of Names and Addresses, but the names are in the "Mr. and Mrs. John Doe" format. I want to be able to sort by last name since the list is sorted by some other criteria. Is there a way to do this even if I have to extract the last name into another column.

I am using Libreoffice on Windows 8.1 English

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 20:16:05.274367

1 Answer

Sort by » oldest newest most voted

answered 2014-10-08 19:22:21 +0200

Lupp gravatar image

updated 2014-10-08 20:59:58 +0200

Assuming the (malformatted) name in A31 =TRIM(RIGHT(A31;LEN(A31)-SEARCH("[:alpha:]+[ ]*$";A31)+1)) will produce the needed helper text. The formula allows for trailing spaces in A31 as these sometimes cause annoying effects. Please note that the only thing taken as delimiter is the last not alphabetic character in front of the last contiguous sequence of alphabetic characters. This means that a "name" like "George Bush junior" will result in a simple "junior" as the value of the sorting key. What about "Henry Ford III"? You see, I would like to suggest reorganising the data source in a reasonable way. If you have the different parts of a name in different cells you may easily concatenate a compound name by a formula. The reverse procedure is much more complicated.

The formula will only work if under

'Tools' > 'Options' > 'LibreOffice Calc' > 'Calculate' > 'General Calculations'

'Enable regular expressions in formulae' is checked.

edit flag offensive delete link more


Thank you. It worked.

Mike gravatar imageMike ( 2014-10-08 20:05:18 +0200 )edit

Question Tools

1 follower


Asked: 2014-10-08 18:46:20 +0200

Seen: 1,466 times

Last updated: Oct 08 '14