Dec 01 2021 02:15 AM
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
Dec 01 2021 02:57 AM - edited Dec 01 2021 03:00 AM
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 :)
Dec 01 2021 04:11 AM
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.
Dec 01 2021 04:55 AM - edited Dec 01 2021 04:59 AM
SolutionHi @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.
Dec 01 2021 06:03 AM - edited Dec 01 2021 07:04 AM
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.
Dec 01 2021 12:08 PM
Dec 01 2021 04:55 AM - edited Dec 01 2021 04:59 AM
SolutionHi @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.