Nov 30 2020 02:05 PM
I'm on a Mac and trying to sort a column by numbers but it's not sorting how you would actually count. For instance, instead of sorting 1 through 20 as you'd normally count, it is sorting 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9.
The column is mixed text/number and I've tried selecting the column as text, number, general, etc. and it doesn't change the sorting to a natural count style.
Any help is appreciated, thank you!
Nov 30 2020 11:01 PM
@AWitt77 You need to transform all entries in the column to real numbers. Simply setting the format is not sufficient. Select the column with the "numbers". On the Data ribbon select "Text to Columns".
Click "Next". until you reach step 3 of 3, Make sure "General" is selected. Press Finish. Now you will have real numbers that can be sorted as you expect then to be.
Dec 01 2020 11:20 AM
@Riny_van_Eekelenthank you so much for the help!!!
Apr 23 2024 08:11 PM
I have the following data in a column. 1, 1A, 2, 1B. 401B, 400. I would like them sorted as 1, 1A, 1B, 2, 400, 401B. How do I create this 'natural sort' ? Thanks
Apr 25 2024 03:30 PM
@paulcechovicgmailcom See the attached workbook. Excel 365 or Excel for the web is required. Be sure to read the _Info worksheet.
Apr 25 2024 10:12 PM
@SnowMan55 Thank you very much for your efforts at helping me solve this sort problem. I am not very technical, but I will attempt to follow your instructions. Do I paste the formula in D2 into a blank column D and then replicate it? Sorry if this sounds stupid. Thanks again for your help.
Apr 26 2024 09:47 AM
It does not sound stupid; everyone starts learning Excel at the novice level.
And let me be clear that this is not Excel's Sort feature, which sorts a range of data in place. A formula that includes a SORT or SORTBY function makes a copy of a range, with rows sorted however is specified in its arguments.
Fortunately, you need only one formula per sorted range, because the formula spills values into adjacent columns and rows. You can see that it spills from a visual indicator – at least on the Windows version, hopefully the Mac version also – because when you select the cell with the formula, Excel draws a (temporary) thin blue boundary around the entire range into which it placed values (and a grayed-out copy of the formula, if you look in the Formula Bar when one of those other cells in the range is selected).
Here's the formula again, for reference:
=LET( IDs, $A2:$A7, data_to_sort, $A2:$B7,
numbers, LET( digits_only, LAMBDA(string, LET(
explode, TOCOL(MID(string, SEQUENCE(LEN(string)), 1) * 1, 2),
IFERROR(CONCAT(explode) * 1, "")
) ), BYROW(IDs, digits_only) ),
alpha, MID(vector, LEN(numbers)+1, 25),
SORTBY(data_to_sort, numbers, 1, alpha, 1)
)
So:
¹ If any cells in the range where you try to put the copy are not empty, the formula will generate a #SPILL! error. That's OK, because Excel has protected you from overwriting some existing formulas/values. Once you clear those cells, the formula will do its work.
² In the example in my workbook, the Identifiers were in the leftmost column of the data range. The formula works even if the Identifiers are in some other column.
If you want, it may be possible to overwrite the original range with the sorted range. But do not attempt this if the original range contains any formulas. First save your work, in case something goes wrong. You would select all of the sorted range, press Ctrl+C (or Cmd+C, if on a Mac?), right click on the top left cell of the original range, and click the Paste Values icon on the popup menu (or Paste Special… and select the Values option).
Apr 26 2024 02:36 PM
@Riny_van_Eekelen Hi Riny: I have tried your solution on some test data and IT WORKS. I am very appreciative of your help. thank you very much. Paul
Apr 29 2024 02:45 PM