Sort 8 position alphanumeric correctly

[hawgmech]

Hi,

I have an excel list in which column A contains an alphanumeric value that can be between 6-8 position long. The 5th position will be a letter (J or P). all other position is a number (0-9). An example would be 3410P10 or 9413J101. My issue is that when I go to sort, they are not sorting according to numerical value after the letter.

For example, I have:

2730J1
2730J10
2730J2

I would like the be able to select the sort button or the custom sort and sort this correctly so that it looks like this:

2730J1
2730J2
2730J10

I'm a novice when it comes to excel, but I'm creating this listing for other people in the office to use that can add additional numbers and will require to sort.

Is there a way to "correct" the sort without the need to add additional columns or break the values up into its sub components?

Thanks

 

[Dave87]

try pasting this into cell b2
=RIGHT(A2,LEN(A2)-5)

this should sort correctly for you

 

[HappierThan]

You could keep them all the same length by adding leading zeroes and then sort.

2730J01

2730J02

2730J10

 

[Dave87]

to get all sort items in three digit form use the following update

=TEXT(RIGHT(A2,LEN(A2)-5),"000")