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

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!

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)
``````