Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numeric and alpha entries
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. -- Thanks, Allison |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numeric and alpha entries
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. On Sep 26, 4:02 pm, Allison wrote: 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. -- Thanks, Allison |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting numeric and alpha entries
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? -- Thanks, Allison "Pete_UK" wrote: 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. On Sep 26, 4:02 pm, Allison wrote: 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. -- Thanks, Allison |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Alpha-Numeric Values | Charts and Charting in Excel | |||
Sorting Cells With Alpha Numeric Values | Charts and Charting in Excel | |||
Sorting Alpha Numeric Part 2 | Excel Discussion (Misc queries) | |||
Sorting Alpha Numeric | Excel Discussion (Misc queries) | |||
Alpha-Numeric Sorting | Excel Discussion (Misc queries) |