Forum Discussion
How do I sort by number in cells containing text?
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.
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.
