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 4.3.1.2 on Windows 8.1 English
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.
Thank you. It worked.