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?
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?
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
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
Gary's Student
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks