I’m still fairly new in converting over from Microsoft to Libre and, thus far, am impressed. But I now have a problem. On an Excel, I have a list of 1000 3-digit numbers on a spreadsheet; have a formula to not permit duplicate entries and it all works. So far, so good in Libre. However, I am adding some duplicates and placing a letter at the end. I need it to sort like this: 091, 150, 163, 163-a, 175, 199, 235, 235-a, etc. When I click “sort,” it places the "-a"s at the very end so I would have this, 091, 150, 163, 175, 199, 235, 163-a, 235-a. Does anyone have an idea of how I can get it to sort properly? I did try changing the cell format to text which did not work and I tried deleting the formula and that also did not work–both options sorted the same way. Thanks for any help someone can provide.
As an altenative, instead of a letter, add a decimal digit; i.e., 235, 235.1. They all will be numbers, and you can sort them.
Additionally you can format those cells with a space (000_m), and create a personal style to apply with conditional formatting (Formula is A1-INT(A1)>0) to display the decimal place.
See sample file (9.5 KB).
Gee, didn’t think of that–I will try it; however, I have it set to not permit duplicate “3-digit” numbers which is important.
LeroyG, nice and clean! I use decimals to flag things on occasion, so I’ll be sure to “steal” this so they standout. But that’s not why I’m tacking this comment on. Rather…
Can you check to see what happens on your Numeric-alpha sort sheet when you right-click your data range and select Conditional Formatting…? I get the notification dialog to overlap or to change, but if I click No I get no chance for overlap, and if I click Yes I’m not seeing the conditional formatting that is actually there already (to edit it), but just a ‘suggestion’ to use Accent style. Am I misunderstanding the notification dialog? I’m using 7.2.2.2 and regular Format-Condition-Manage works just as expected.
The same behavior here if partial range is selected (with version 7.1.7.2 on Linux 4.12 right now).
But it works if the complete range is selected (Ctrl+* select, if the range is isolated).
Make sure all your cells are of type Text, not numeric.
- select/mark the cell range in question
- apply the Text number format
- keep the selection on the range
- invoke the Find&Replace dialog (Ctrl+H)
-
Find:
^. -
Replace:
$0 - in Other options enable:
-
- Current selection only
-
- Regular expressions
- hit Replace All button
All cell content of selected cells will be “re-entered” as text content.
After which you can invoke Sort with option natural sort (for example in case 091 was actually numeric 91 and only displayed with a leading 0).
BTW, Excel’s Range.Replace method ignores the cell’s text format.
Example. Cell A1 has text format and contains the text 1. After replacing 1 to 1, the cell will contain the number 1 (the text format will remain). 
THANK YOU, THANK YOU! That works beautifully! The only inconvenience is that when all is said and done, it appears I will have to add a zero to the front of number “000” through “099” to maintain the 3-digit format but that is minor in comparison! Cannot see how to mark this as “Solved,” but it is.
That’s a piece of dirt. That behaviour makes it a one-way road, you can convert from text to numeric, but not from numeric to text. (unless there’s some other flag/option/magic/mystery to solve that again).
I think the developers of the Range.Replace method simply forgot that a cell can be in text format.