# Sort AlphaNumeric Text By Numbers

• Hi

Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3

How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?

Thanks.

• Re: Sort AlphaNumeric Text By Numbers

However, Due to circumstances, I am not allowed to create an additional column. Would there be a macro to do all this together?

Cheers

• Re: Sort AlphaNumeric Text By Numbers

Quote from dtwk

...However, Due to circumstances, I am not allowed to create an additional column. ...

Not even temporarily? Add the column with the formula, sort, delete the added column?

Regards,

• Re: Sort AlphaNumeric Text By Numbers

dtwk,

Is it always 3 letters then numbers?

dr

• Re: Sort AlphaNumeric Text By Numbers

Yes dr.
Its the same ABC followed by a set of numbers.

Cheers

• Re: Sort AlphaNumeric Text By Numbers

If it's always "ABC followed by a set of numbers" you can use a normal sort.

• Re: Sort AlphaNumeric Text By Numbers

Dave

ABC10 will still come before ABC9 even thought 9 < 10... =(

• Re: Sort AlphaNumeric Text By Numbers

How about pasting the column to a new worksheet, taking off the "ABC", sorting, putting the ABC back in, and pasting back to the original sheet?

• Re: Sort AlphaNumeric Text By Numbers

Hi DTWK -

Clarify again -
are the first 3 letters ALWAYS "ABC"?
are the numbers ALWAYS 3 digits?
would it be acceptable to change the numbers into 3 digit numbers where by ABC9 would become ABC009

If all of the above are true, its not that difficult to write VBA, if either rule is broken, it becomes a little more complicated to write a generic routine.

Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: Sort AlphaNumeric Text By Numbers

Use this code

• Re: Sort AlphaNumeric Text By Numbers

or maybe just something simple like

Code
``````Sub sortwithletters()
With Range("a1", Range("a1").End(xlDown))
.Replace What:="ABC", Replacement:="99999999", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Replace What:="99999999", Replacement:="ABC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub``````
• Re: Sort AlphaNumeric Text By Numbers

nice idea....

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: Sort AlphaNumeric Text By Numbers

dtwk,

Another way. Uses blank rows on the sheet to split and sort.

This version is for a single column of data

This version works on multiple columns of data

Cheers,

dr

• Re: Sort AlphaNumeric Text By Numbers

or maybe just something simple like

Code
``````Sub sortwithletters()
With Range("a1", Range("a1").End(xlDown))
.Replace What:="ABC", Replacement:="99999999", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Replace What:="99999999", Replacement:="ABC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub``````

DOH!

• Re: Sort AlphaNumeric Text By Numbers

Nice Badger... It works perfectly... Thanks a lot to all u guys... been a great help since i registered to this website... Thanks to Dave and all... /bow /salute

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!