How to Sort Mixed Numbers and Text in a column?

[mvallance12341120]

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

[#2 · Accepted solution]

@mvallance12341120 

You 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

[#3]

@Subodh_Tiwari_sktneer 

That worked. Thank you very much ... and the example file helped so much.

Stay safe.

Michael

[#4]

You're welcome Michael! Glad it worked as desired.

Thanks Michael and you too!