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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top