Sep 04 2020 03:27 AM
Hello. I would like to ask how to Sort Mixed Numbers and Text in a column.
For instance, I have 300 students all listed as
FIRST NAME student number LAST NAME
such as
Michael 1234567 Vallance
And I have over 100 entries of 10 columns of data.
I want to sort the students by number.
Can you advise please?
Thank you from a very busy online teacher.
Michael
Sep 04 2020 03:40 AM
SolutionYou may insert a helper column and use the following formula to extract the Student Numbers in the helper column and then you may sort the helper column either ascending or descending as per your requirement.
The attached is a file with some dummy data, in which column K is the helper column called Student Number and it is sorted in Ascending order. You can replicate the same in your original file.
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2),LEN(A2)))+0
Sep 04 2020 06:09 AM
That worked. Thank you very much ... and the example file helped so much.
Stay safe.
Michael
Sep 04 2020 09:00 AM
You're welcome Michael! Glad it worked as desired.
Thanks Michael and you too!
Sep 04 2020 03:40 AM
SolutionYou may insert a helper column and use the following formula to extract the Student Numbers in the helper column and then you may sort the helper column either ascending or descending as per your requirement.
The attached is a file with some dummy data, in which column K is the helper column called Student Number and it is sorted in Ascending order. You can replicate the same in your original file.
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2),LEN(A2)))+0