Forum Discussion
Order rows ascending by number with text that include 'numbers and letters'
Hello,
I have a excel file with several columns and on the first column I have rows with repeated text that include numbers and 'numbers and letters' (1(several times), 5D(several times), 10A(several times), 10B(several times), 900L(several times), etc...) but out of order.
How can I order them by number ignoring the letter?
I have been searching online for a while and never found a solution for this specific situation. Any help would be greatly appreciated.
As info I am a basic user and I don't know how to use macros or coding.
Thank you very much guys. I figured it out with the helper column. I couldn't make the regexextract work but I found another way. I am too lazy to investigate SQL and how it works.
I am sure the way I found it's not the most direct... but it works.
Data on column A
1. Column B (to determine the position of last digit)
=MAX(ISNUMBER(VALUE(MID(A2;{1;2;3;4;5;6;7;8;9};1)))*{1;2;3;4;5;6;7;8;9})+12. column C (to separate the numbers)
=VALUE(LEFT(A2;B2-1))3. column D (to separate the letters)
=TRIM(RIGHT(A2;LEN(A2)-B2+1))Then I just order by number
4 Replies
Thank you very much guys. I figured it out with the helper column. I couldn't make the regexextract work but I found another way. I am too lazy to investigate SQL and how it works.
I am sure the way I found it's not the most direct... but it works.
Data on column A
1. Column B (to determine the position of last digit)
=MAX(ISNUMBER(VALUE(MID(A2;{1;2;3;4;5;6;7;8;9};1)))*{1;2;3;4;5;6;7;8;9})+12. column C (to separate the numbers)
=VALUE(LEFT(A2;B2-1))3. column D (to separate the letters)
=TRIM(RIGHT(A2;LEN(A2)-B2+1))Then I just order by number
If need nature compare alternaively:
sql:
select * from Sheet1;
select * from Sheet1 order by f01 COLLATE NATURAL_CMP;
To flesh out Harun24HR's solution a bit, if you are happy to leave the source data unaltered and use an Excel 365 formula to generate the sorted results in a fresh table
= SORTBY(table, VALUE(REGEXEXTRACT(column1, "^\d+")))- If you want to sort on same columns then you need a additional helper column. On that helper column extract numbers using function then sort by that column.
- If you want to sort data to other places, then you can use SORTBY() function bases on a virtual column made by formula.
