# Sorting Issue?

#### kale_81

##### New Member
Hello all:

I want to thank those of you who helped me with my last question, you are all wonderful resources. I have, however, come across a problem I cannot seem to solve, and must ask for some further help.

I'm trying to sort a string in the following format: HB23 The problem is that the two letters might be followed by a number between 1 and 9999. Sorting turns up the following:

HB23
HB25
HB36
HB378
HB39
HB41
HB4230
HB43
HB45

I've read how to solve sorting alphanumeric strings, but no matter what I do it sorts these strings like so, referencing the each number as they come first. I've tried formatting the cells using a custom format, and have tried to add 0's before the numbers ie: 3 => 0003 But that doesn't seem to work with the "HB" in front of the number. I've also tried splitting the letters and numbers into columns and then formatting the numeric cells to add 0's in front of them but that isn't working either. The cells simply will not allow me to sort the numbers properly.

Has anyone had any experience with this?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Richard Schollar

##### MrExcel MVP
You should be OK if you split the numeric part out (into a separate column) and ensure you convert it to a value before the sort. Something along the lines of

=VALUE(RIGHT(A1,LEN(A1)-2))

You can then sort ascending based on this numeric part which should result in the sort order you want.

Regards

Richard

Replies
6
Views
143
Replies
2
Views
79
Replies
2
Views
81
Replies
1
Views
122
Replies
0
Views
75