Alpha numeric sort

1. Alpha numeric sort

I am trying to sort alpha numeric data like so -

PA1
PA2
PB1
PB2
PB10

When I sort, the alphabet part is sorted right, but the number part is is not what I want -

PB10 is right below PB1-

PB1
PB10
PB2....

Any work around?

2. Re: Alpha numeric sort

You would need to insert leading zeros to your numbers, like this:

PA01
PA02
PB01
PB02
PB10

If you have a lot of these then you could use a formula like this:

=IF(ISNUMBER(RIGHT(A1,2)+0),A1,LEFT(A1,LEN(A1)-1)&TEXT(RIGHT(A1),"00"))

assuming the list starts in A1, and then use the column with the formula in as the sort field, and then you could remove that column.

Hope this helps.

Pete

3. Re: Alpha numeric sort

With your data in Column A, In B1 enter:

=LEFT(A1,2) and copy down

In C1 enter:

=--MID(A1,3,9999) and copy down

Then sort all three columns, first by column B and then by column C

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1