How do you sort a column of numbers with hyphens?
Example ##-##-####.
I want to sort by the last numbers
Thanks
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How do you sort a column of numbers with hyphens?
0
Answers
1
Unfortunately this can't be done because what you are sorting aren't numbers, technically, but text strings. The trick would be to separate the final digits from the leaders and use them to sort by.
- I entered a few hyphen-sparated "numbers" in column A, starting from A2
- I named the A column "Numbers" and the B column "Helper"
- I entered this formula in B2.
and copied down.=MID($A2,FIND(CHAR(160),SUBSTITUTE($A2,"-",CHAR(160),2))+1,20)
Note that the output of this formula are strings, too. - I selected the entire table and clicked Custom Sort from the Sort & Filter Menu (Ribbon's Home tab > Editing)
- My data have headers
- Sort by column "Helper" - OK
- Sort anything that looks like a number as a number - OK
- Delete the Helper column
The Numbers and Helper columns could be anywhere on the sheet and have any names you prefer. There could be other data on the sheet. So long as they are all selected rows will stay together after the sort.
Discussion
Hello sam, I wonder if you got your sort to work. In fact, I wonder if the hyphens you are dealing with are perhaps induced by the cell format. If so, you would still need a helper column to sort by the last 4 digits but the formula would be a lot simpler, just =Right($A2, 4)
Variatus (rep: 4889) Jun 15, '19 at 8:03 pm