Sorting Issue?

kale_81

New Member
Joined
May 23, 2005
Messages
7
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?
 

Some videos you may like

Excel Facts

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,842
Members
412,420
Latest member
grace_abar
Top