Sort 8 position alphanumeric correctly

hawgmech

New Member
Joined
Apr 28, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Dave87

Board Regular
Joined
Apr 22, 2020
Messages
105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
try pasting this into cell b2
=RIGHT(A2,LEN(A2)-5)

this should sort correctly for you
 

HappierThan

Board Regular
Joined
Mar 19, 2020
Messages
53
Office Version
  1. 2016
You could keep them all the same length by adding leading zeroes and then sort.

2730J01
2730J02
2730J10
 

Dave87

Board Regular
Joined
Apr 22, 2020
Messages
105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
to get all sort items in three digit form use the following update

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

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,479
Members
412,596
Latest member
nickthebizz
Top