How to turn a series of hyphenated and un-hypenated numbers into a shortened hyphenated range?

[freedom123]

Basically I have a bunch of numbers in a list. Each number/hypehnated number is in it's own row. Some are by themselves some are in hyphenated ranges.
I want to put them all together into one shortened hyphenated range if that makes any sense

So for example

001, 002-010, 010-055, 059, 061, 063-067 will become 001-055, 059, 061, 063-067 after everything.

 

[Ezguy4u]

Free Let's get the ball rolling. Now this is my solution to your problem. It's probably not the best solution but we have to start somewhere. Usually this generates more questions than answers so let the discussion begin.

Book1
	A	B	C
2	001
3	002-010
4	010-055
5	059		001, 002-010, 010-055, 059, 061, 063-067
6	061
7	063-067
Data

Cell Formulas
Range	Formula
C5	C5	=CONCATENATE(A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7)

 

[freedom123]

Free Let's get the ball rolling. Now this is my solution to your problem. It's probably not the best solution but we have to start somewhere. Usually this generates more questions than answers so let the discussion begin.

Book1
	A	B	C
2	001
3	002-010
4	010-055
5	059		001, 002-010, 010-055, 059, 061, 063-067
6	061
7	063-067
Data

Cell Formulas
Range	Formula
C5	C5	=CONCATENATE(A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7)

Hello, thank you.
I've actually been using a slightly similar formula, the TEXTJOIN() formula and manually shortening the TEXTJOIN() string.
Hopefully someone has some insight on this

 

[ISY]

Hi

freedom123​

The data you showed as expected is not consistent with the picture

 

[shaowu459]

Please try this one and let me know if there is any bug .

shortened hyphenated range.xlsx
	A	B	C
1	001
2	002-010		结果
3	011-055		001-055,059,061,063-067,073-075,099,101-103
4	059
5	061
6	063-067
7	073-075
8	099
9	101
10	102
11	103
Sheet1

Cell Formulas
Range	Formula
C3	C3	=REDUCE(,A1:A11,LAMBDA(x,y,IF(LEFT(y,3)-RIGHT(x,3)=1,IF(LEFT(RIGHT(x,4))="-",LEFT(x,LEN(x)-4),x)&"-"&RIGHT(y,3),x&","&y)))

 

[Ezguy4u]

So Free, If I am understanding correctly, you are using the below example and function. So what is the problem. Does column A have lots of data and takes a long time to set up? Is the answer not in the form you want? Let us know.

Book1
	A	B	C
2	001
3	002-010
4	010-055		001, 002-010, 010-055, 059, 061, 063-067
5	059
6	061
7	063-067
Sheet1 (3)

Cell Formulas
Range	Formula
C4	C4	=TEXTJOIN(", ",TRUE,A2,A3,A4,A5,A6,A7)

 

[dreid1011]

Hi

freedom123​

The data you showed as expected is not consistent with the picture

So Free, If I am understanding correctly, you are using the below example and function. So what is the problem. Does column A have lots of data and takes a long time to set up? Is the answer not in the form you want? Let us know.

Book1
	A	B	C
2	001
3	002-010
4	010-055		001, 002-010, 010-055, 059, 061, 063-067
5	059
6	061
7	063-067
Sheet1 (3)

Cell Formulas
Range	Formula
C4	C4	=TEXTJOIN(", ",TRUE,A2,A3,A4,A5,A6,A7)

I believe the goal is to combine consecutive ranges into a single range so long as there is not a skipped value between them.

IE: 001, 002-010, 010-055 can be represented as 001-055 because there is no skipped value between each range. If rows 6 and 7 had the values of 062 and 063-067, you could represent that in a single range as 062-067, but because row 6 has the value 061, they will be two separate ranges because it skips 062 between them.

That being said, I don't really have an idea how to solve the problem, just thought I could help clarify what is being asked.

Edit: However, it looks like shaowu459 has a solution.

 

[Ezguy4u]

dreid yes I see that now. And it does look like shaowu has the solution. Now we just need free to weigh in and tell us.