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.
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, Christine
PS 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, ..
- Thank you!
