Question related to sort letters and numbers

Regular Contributor

Hello Everyone,

I have a Data

like - 

Screenshot (5348).png


After sorting, it come Person 1, Person 101, Person 3, Person 4 and Person 5

like - 

Screenshot (5349).png


It will not sort ascending order like Person 1, Person 3, Person 4,  Person 5 and Person 101


So, How to sort by ascending order by using EXCEL FORMULA or VBA CODE?

Please help..??


(I am using Microsoft Office 2019 Home and Student)






3 Replies


You may add helper columns extracting numbers only and sort range by it.

How can we do it sir?


Slightly depends on your Excel version and actual data.

Helper column could be added as


when select both and Data -> Sort. Result is