1

How do I create a list of numbers, where consequential numbers are reduced to being separated by a hyphen? e.g. I have a column with numbers like

1
2
3
5
6
7
8
10
12

I want this to be reduced to 1-3,5-8,10,12. I found this solution for Excel, which includes VBA: https://superuser.com/questions/971161/list-range-of-numbers-in-excel

Of course if this is solvable with just functions (perhaps query could help?), it would be nice!

| | | | | |
0

You can do it using formulas but you'll need 2 extra columns.

  • Let's assume that column A features this list from A1 till A16 (it must be sorted in ascending order from top to bottom):

    1
    2
    3
    5
    6
    7
    8
    10
    12
    13
    14
    16
    18
    21
    22
    23
    
  • In such case, at column B select B1 and put the formula =A1, then select B2 and put the formula =if(eq(A1+1,A3-1),if(B1="-","","-"),if(A3>A2+1,A2&",",A2)), then copy B2 and paste from B3 till B16 in order to expand the formula. The result will be this:

    =A1
    =if(eq(A1+1,A3-1),if(B1="-","","-"),if(A3>A2+1,A2&",",A2))
    =if(eq(A2+1,A4-1),if(B2="-","","-"),if(A4>A3+1,A3&",",A3))
    =if(eq(A3+1,A5-1),if(B3="-","","-"),if(A5>A4+1,A4&",",A4))
    =if(eq(A4+1,A6-1),if(B4="-","","-"),if(A6>A5+1,A5&",",A5))
    =if(eq(A5+1,A7-1),if(B5="-","","-"),if(A7>A6+1,A6&",",A6))
    =if(eq(A6+1,A8-1),if(B6="-","","-"),if(A8>A7+1,A7&",",A7))
    =if(eq(A7+1,A9-1),if(B7="-","","-"),if(A9>A8+1,A8&",",A8))
    =if(eq(A8+1,A10-1),if(B8="-","","-"),if(A10>A9+1,A9&",",A9))
    =if(eq(A9+1,A11-1),if(B9="-","","-"),if(A11>A10+1,A10&",",A10))
    =if(eq(A10+1,A12-1),if(B10="-","","-"),if(A12>A11+1,A11&",",A11))
    =if(eq(A11+1,A13-1),if(B11="-","","-"),if(A13>A12+1,A12&",",A12))
    =if(eq(A12+1,A14-1),if(B12="-","","-"),if(A14>A13+1,A13&",",A13))
    =if(eq(A13+1,A15-1),if(B13="-","","-"),if(A15>A14+1,A14&",",A14))
    =if(eq(A14+1,A16-1),if(B14="-","","-"),if(A16>A15+1,A15&",",A15))
    =if(eq(A15+1,A17-1),if(B15="-","","-"),if(A17>A16+1,A16&",",A16))
    
  • Finally, select the cell C1 and put this formula:

    =CONCATENATE(B1:B16)
    
| | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.