Sorting an Excel Column by value

Question

I have a column with ID values, like these:

I would like to sort it by "digit value" using VBA, so I get a list like this

Since numbers should come before letters, and digits' relevance comes from their position. However, if I use Range().Sort I get a list like this:

Which is how Excel sorts it alphabetically. Is there a way to sort it the way I want it, short of writing a sort function myself?

You can add helper column with formula =""&A1 and sort using it. — BrakNicku
– BrakNicku, Commented Sep 16, 2015 at 11:02

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

If you format all of the values as Text you will be able to sort using the Sort numbers and numbers store as text differently option. This produces the results you are looking for.

Of course, you will lose the benefit of dealing with true numbers and will have to watch out for lookups that are seeking a true number and not text-that-looks-like-a-number but as your values are already mixed, this might not be a bad thing.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 16, 2015 at 11:05

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

MKII Over a year ago

But how can I force a VBA script to view the cell's content as a Text?

user4039065 Over a year ago

Use VBA to convert them into text and put the Range.Sort method's DataOption:=xlSortNormal option.

Collectives™ on Stack Overflow

Sorting an Excel Column by value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related