How to create comma and hyphen separated list of sequential numbers in Sheets?

Question

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!

Answer 1

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)