Sorting numeric and alpha entries

[Allison]

I have a list that contains a field for road number. It sorts like the left
column, but I want it to look like the right column:

Road Number Road Number
2 1A
11 11
21 100
100 101
101 1000
200 10000
201 10000A
1000 2
10000 2A
10000A 2B
1A 200
2A 201
2B etc

I have formatted the data to be text, but that doesn't deliver the sort
order that I want. I am hoping that I can use formulas to do this, as I am
not familiar with VBA. Thanks in advance for your help.

 

[Pete_UK]

What you need to do is add sufficient leading zeros to the numbers to
make them all the same length - you can do this in a helper column,
and then sort using the helper column as the sort field. After the
sort you can then delete the helper column, leaving the original Road
Number column unchanged.

If you want a formula to add the leading zeros, can you confirm:

a - the column where the Road Number data is
b - the first empty column in your data
c - the row containing the first Road Number
d - the number of rows used by the data
e - that you will only ever have a single letter after the number (eg
"A", "B" etc)

Hope this helps.

 

[Allison]

Pete_UK:

Thanks for responding. I tried your suggestion manually on some test data,
and entered leading zeros so that the length of each road number was the same
number of characters. I sorted and ended up with the same result, eg, 0002,
0011, 0021, 001A. Am I missing something?