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

@Zan_Hanifee 

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

How can we do it sir?

@Zan_Hanifee 

Slightly depends on your Excel version and actual data.

Helper column could be added as

image.png

when select both and Data -> Sort. Result is

image.png