SOLVED

How to Sort Mixed Numbers and Text in a column?

%3CLINGO-SUB%20id%3D%22lingo-sub-1635208%22%20slang%3D%22en-US%22%3EHow%20to%20Sort%20Mixed%20Numbers%20and%20Text%26nbsp%3Bin%20a%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1635208%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I%20would%20like%20to%20ask%20how%20to%20Sort%20Mixed%20Numbers%20and%20Text%26nbsp%3Bin%20a%20column.%3C%2FP%3E%3CP%3EFor%20instance%2C%20I%20have%20300%20students%20all%20listed%20as%3C%2FP%3E%3CP%3EFIRST%20NAME%20student%20number%20LAST%20NAME%3C%2FP%3E%3CP%3Esuch%20as%3C%2FP%3E%3CP%3EMichael%201234567%20Vallance%3C%2FP%3E%3CP%3EAnd%20I%20have%20over%20100%20entries%20of%2010%20columns%20of%20data.%3C%2FP%3E%3CP%3EI%20want%20to%20sort%20the%20students%20by%20number.%3C%2FP%3E%3CP%3ECan%20you%20advise%20please%3F%3C%2FP%3E%3CP%3EThank%20you%20from%20a%20very%20busy%20online%20teacher.%3CBR%20%2F%3EMichael%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1635208%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1635259%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Sort%20Mixed%20Numbers%20and%20Text%26nbsp%3Bin%20a%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1635259%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782095%22%20target%3D%22_blank%22%3E%40mvallance12341120%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20insert%20a%20helper%20column%20and%20use%20the%20following%20formula%20to%20extract%20the%20Student%20Numbers%20in%20the%20helper%20column%20and%20then%20you%20may%20sort%20the%20helper%20column%20either%20ascending%20or%20descending%20as%20per%20your%20requirement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20is%20a%20file%20with%20some%20dummy%20data%2C%20in%20which%20column%20K%20is%20the%20helper%20column%20called%20Student%20Number%20and%20it%20is%20sorted%20in%20Ascending%20order.%20You%20can%20replicate%20the%20same%20in%20your%20original%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTRIM(MID(SUBSTITUTE(A2%2C%22%20%22%2CREPT(%22%20%22%2CLEN(A2)))%2CLEN(A2)%2CLEN(A2)))%2B0%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

3 Replies
Highlighted
Best Response confirmed by mvallance12341120 (New Contributor)
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

 

 

Highlighted

@Subodh_Tiwari_sktneer 

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

Stay safe.

Michael

Highlighted

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

 

Thanks Michael and you too!