Forum Discussion
How do I sort by number in cells containing text?
Hi
How can I sort a column of cells that contain the format number text numerically? I have read that I need to change the filter to numerical rather than A–Z, but I can't work out how to do that.
At the moment, if I use the sort function, it pulls out all the numbers starting with 1, then 2, etc. I would like a true numerical reordering.
I am using Version 16.54 on a Mac.
Many thanks.
Christine
Hi Christine_S_ ,
I have seen the screenshot of you dataset and the value that are stored though they look numbers but stores are text, when you sort the data it read 1 and look next sort sequence 11 and so on. Solution to this problem you need add one leading zero to your dataset for the single digit or two zeros depending on you length of number. Using the below formula in the helper column for single digit.
=SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2,1)-1),TEXT(LEFT(A2,FIND(" ",A2,1)-1),"00"))Attaching the sample file for your ready reference.
Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.
6 Replies
Hi Christine_S_ ,
I have seen the screenshot of you dataset and the value that are stored though they look numbers but stores are text, when you sort the data it read 1 and look next sort sequence 11 and so on. Solution to this problem you need add one leading zero to your dataset for the single digit or two zeros depending on you length of number. Using the below formula in the helper column for single digit.
=SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2,1)-1),TEXT(LEFT(A2,FIND(" ",A2,1)-1),"00"))Attaching the sample file for your ready reference.
Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.
Hi Faraz
Of course! Why didn't I think of that! Thank you so much – you've saved me loads of time!
I really appreciate your help. Regards, ChristinePS Special thanks for the formula. I'd never have been able to find that without your help.
- BTW, if you don't like the 01 look a space before the number will also work so it looks like 1, 2, ... , 10, ..., 20, ..
he FILTER function allows you to filter a range of data based on criteria you define.
How To Filter Values By First Letter Or Last Character In Excel?
Examples and detailed explanations can be found in the links above.
I also wish you have fun with excel 🙂
Hi Nikolino
Thanks for this link, but it doesn't tell me how to sort by number... That's what I need to know. As you'll see from my screenshot, the main sort function only picks the first numeral in the number, but I need to sort by whole number – ie from 1 to 46. Is this possible?
Many thanks.
