How do you sort a column of numbers with hyphens?

0

How do you sort a column of numbers with hyphens?
Example ##-##-####.
I want to sort by the last numbers
Thanks

Answer
Discuss

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.

  1. I entered a few hyphen-sparated "numbers" in column A, starting from A2
  2. I named the A column "Numbers" and the B column "Helper"
  3. I entered this formula in B2.
    =MID($A2,FIND(CHAR(160),SUBSTITUTE($A2,"-",CHAR(160),2))+1,20)
    and copied down.
    Note that the output of this formula are strings, too.
  4. I selected the entire table and clicked Custom Sort from the Sort & Filter Menu (Ribbon's Home tab > Editing)
  5. My data have headers
  6. Sort by column "Helper" - OK
  7. Sort anything that looks like a number as a number - OK
  8. 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.

Discuss

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: 2938) Jun 15, '19 at 8:03 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login