Question related to Sort

%3CLINGO-SUB%20id%3D%22lingo-sub-2979703%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20Sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979703%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EThis%20is%20my%20data%3C%2FP%3E%3CP%3Elike%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(5348).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327912i9AB959F31C63792B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(5348).png%22%20alt%3D%22Screenshot%20(5348).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20sorting%2C%20it%20will%20come%20like%20this%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(5349).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327913iDB69B6E49534F09A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(5349).png%22%20alt%3D%22Screenshot%20(5349).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20come%20like%20this%20%3A%3C%2FP%3E%3CP%3EPerson%201%2C%26nbsp%3BPerson%203%2C%26nbsp%3BPerson%204%2C%26nbsp%3BPerson%205%2C%26nbsp%3BPerson%20101%3C%2FP%3E%3CP%3ESo%2C%20what%20should%20i%20write%20formula%20which%20sort%20in%20ascending%20order%26nbsp%3BPerson%201%2C%26nbsp%3BPerson%203%2C%26nbsp%3BPerson%204%2C%26nbsp%3BPerson%205%2C%26nbsp%3BPerson%20101%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2979703%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-2979759%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979759%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EStrings%20(letter%20series)%20are%20sorted%20as%20strings%2C%20that%20is%2C%20alphabetically.%20I%20see%20twee%20solutions%3A%3CBR%20%2F%3E-%20using%20leading%200%20in%20the%20number%3CBR%20%2F%3EPerson%20001%2C%20Person%20002%20etc%3CBR%20%2F%3E-%20or%20use%20just%20numbers%20(without%20%22Person%20%22)%3CBR%20%2F%3EKind%20regards%3CBR%20%2F%3EHans%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2979760%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979760%22%20slang%3D%22en-US%22%3EThank%20you%20for%20giving%20a%20solution%20sir.%3CBR%20%2F%3ECan%20you%20please%20explain%20example%20please%3F%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

This is my data

like - 

Screenshot (5348).png

 

After sorting, it will come like this - 

Screenshot (5349).png

 

I want to come like this :

Person 1, Person 3, Person 4, Person 5, Person 101

So, what should i write formula which sort in ascending order Person 1, Person 3, Person 4, Person 5, Person 101?

 

Please help..??

4 Replies

Hi,
Strings (letter series) are sorted as strings, that is, alphabetically. I see two solutions:
- using leading 0 in the number
Person 001, Person 002 etc
- or use just numbers (without "Person ")
Kind regards
Hans

Thank you for giving a solution sir.
Can you please explain example please?

@Zan_Hanifee 

In your example "Person 1" is in cell A2. You can manually enter "1" in cell B2 and then flashfill range B2:B6. Flashfill can be done by pressing ctrl+E when cursor is in cell B3 in this example. 

Then you can apply sort of range A2:B6 in ascending order by column B as shown in attached pdf file.

I don't know what else I can explain...
You gave

Person
Person 1
Person 3
Person 101
Person 4
Person 5

Make that
Person
Person 001
Person 003
Person 101
Person 004
Person 005