Sorting Alphanumeric Data

[Scrappy666]

Hi all,

I was hoping someone could help as the below is proving to be a nightmare!!

At work we have a large number of references which all begin with HTR-
We now have booking 100,000 (HTR-100000), and when we try to sort this list of bookings, HTR-100000 is appearing mid way down the list.
(Before HTR-10000 and after HTR-09999).

We would like this to be sorted so it ignores the HTR- and sorts the references numerically.
Can this be done?


We currently have the information in a pivot, sorting it within here would be great!

Thanks.
Dan.

 

[Jonmo1]

Add a helper column to extract the number only and sort by that column.

Say it's column A that has those values, beginning in Row 2 (Row 1 is headers)
In B2 and filled down, put

=REPLACE(A2,1,4,"")+0

And fill down.

Then sort by column B.

 

[Scrappy666]

Thanks Jonmo,

I'll give it a shot.