ArcMap 10.3.1-Attribute Table Sorting Issues

Question

I have received a shapefile from a third party that contains a column of alphanumeric characters. ArcMap has it listed as a 'string' in the field properties. The numbers range from 1-6000 with a combination of letters after the numbers. So it looks like:

1
10
100
1000
1001

Then the list continues in a random order and it includes letters at the end such as:

1387a
6176b

I'm trying to find a way to sort this properly? Would I be best splitting it and then sorting and concatenating it again? Is there any easier way to sort this column?

Answer 1

You want understand how it work. [a-z] is ordering after [0-9] and text is ordering by left to right position

In fact, if you don't split you have (in ascending)

- 600
- 609
- 6000
- 6001
- 6009
- 600A
- 602B
- 603Z
- 69Z

After splitting, you have (in ascending with same ids)

- 69, "Z"
- 600, ""
- 600, "A"
- 602, "B"
- 603, "Z"   
- 609, ""
- 6000, ""
- 6001, ""
- 6009, ""

Also, if you need ordering with separate number and text part you can create 2 new columns. 1st content numeric part. 2nd content letter part.

Ordering is not persistent

If you need a persistent ordering, you need create New FeatureClass but it's useless. Or fix your id column with fixed len char

So, if you need ordering in one column and fix position you can add "-" after all ids if this id doesn't end by letter (and if you add only one ending letter) and add 0 before number to have a fixed len char based on max character contains on id

- 0069Z
- 0600-
- 0600A
- 0602B
- 0603Z  
- 0609-
- 6000-
- 6001-
- 6009-