Forum Discussion

Copper Contributor

Nov 30, 2020

Sorting a mixed text/number Column by number on a Mac

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,...

Riny_van_Eekelen

Platinum Contributor

Dec 01, 2020

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.

Copper Contributor

Dec 01, 2020

Riny_van_Eekelenthank you so much for the help!!!

paulcechovicgmailcom
Copper Contributor
Apr 24, 2024
AWitt77
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
- SnowMan55
  Bronze Contributor
  Apr 25, 2024
  paulcechovicgmailcom See the attached workbook. Excel 365 or Excel for the web is required. Be sure to read the _Info worksheet.
  2024-04-24 PC sort alphanumerics by prefixed numbers, then alpha 1.xlsx20 KB
  - paulcechovicgmailcom
    Copper Contributor
    Apr 26, 2024
    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.
- peiyezhu
  Bronze Contributor
  Apr 24, 2024
  https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-sort-927-7010-10010-kind-of-values-in-excel/2aabe102-9f53-41ec-b70f-78a5ecff8d84

Resources