Turn number into comma separated series

[Darth Emphatic]

Not sure if this is possible in excel, which is my preferred place to do it, but I could do in another way if needed. I found another thread on the forums with a similar question, that someone then suggests viewing another forum thread, but when I click on that link, it just routes to the message board home page for me:

Turn numbers separated by a dash into comma separated series

Is there any way to turn two numbers within a cell separated by a dash into a series of numbers separated by commas? Here is an example from the actual data BEFORE: ; Enn 6:16-20 ; ; 1Ctcc 6:3-15,50-53 ; ; Ent 4:14-16,27-31 ; ; Edr 28:40-43 ; ; Eefx 5:20,21 ; Newt 14:2-5,10 ; Nubb 16:3-11,41...

www.mrexcel.com

splitting cell data into columns

Hi, This is a piece of a table that I'm working with:International Repricing Model.xlsBCDE2CountryOperative DateCodeRange3Afghanistan18.03.20059370 - 72, 794Albania18.10.200235568, 695Algeria23.07.200421361...

www.mrexcel.com

In a a set of rows, I have numbers like below:

1
4
3
2

What I want to do is convert those into comma delimited sequences starting at the last number in the previous row. The above would look like:

1
2,3,4,5
6,7,8
9,10

 

[PeteWright]

Hi!

Could you please upload a sample Excel file where you have two sheets (for example), one with the original data and one with the "new" data.
Would be much easier to see what your aim is.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a larger sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[lrobbo314]

Book2 (version 3).xlsb
	A	B
1	1	1
2	4	2,3,4,5
3	3	6,7,8
4	2	9,10
Sheet8

Cell Formulas
Range	Formula
B1:B4	B1	=LET(s,A1,dif,SUM($A$1:A1)-s+1,TEXTJOIN(",",1,SEQUENCE(s,,dif)))

 

[lrobbo314]

Another way using VBA.

Sub TCS()
Dim r As Range:         Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim POS As Integer:     POS = 1
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")
Dim tmp As String

For i = 1 To UBound(AR)
    For j = POS To POS + AR(i, 1) - 1
        tmp = tmp & j & ","
    Next j
    SD.Add Left(tmp, Len(tmp) - 1), 1
    tmp = vbNullString
    POS = POS + AR(i, 1)
Next i

r.Offset(, 1).Value = Application.Transpose(SD.keys)
End Sub

 

[cmowla]

This was fun!

New Microsoft Excel Worksheet (2).xlsx
	A	B
1	1	1
2	4	2,3,4,5
3	3	6,7,8
4	2	9,10
Sheet6

Cell Formulas
Range	Formula
B1:B4	B1	=MID(CONCAT(SEQUENCE(A1,1,SUM($A$1:A1)-A1+1)&","),1,LEN(CONCAT(SEQUENCE(A1,1,SUM($A$1:A1)-A1+1)&","))-1)

 

[Joe4]

This was fun!

New Microsoft Excel Worksheet (2).xlsx
	A	B
1	1	5
2	4	2,3,4,5,
3	3	6,7,8,
4	2	9,10,
Sheet6

Cell Formulas
Range	Formula
B1	B1	=MID(CONCAT(SEQUENCE(A1,1,SUM($A$1:A2)-A1+1)&","),1,LEN(CONCAT(SEQUENCE(A1,1,SUM($A$1:A2)-A1+1)&","))-1)
B2:B4	B2	=CONCAT(SEQUENCE(A2,1,SUM($A$1:A2)-A2+1)&",")

Doesn't appear to be quite right.
B1 shows "5" instead of "1", and you have extra commas at the end of the rest of the rows.

 

[cmowla]

Doesn't appear to be quite right.
B1 shows "5" instead of "1", and you have extra commas at the end of the rest of the rows.

I just fixed it. (I noticed that too, thanks!)

 

[lrobbo314]

And Power Query, why not?

Book2 (version 1).xlsb
	A	B	C	D
1	Nums		Nums	Custom
2	1		1	1
3	4		4	2,3,4,5
4	3		3	6,7,8
5	2		2	9,10
Sheet9

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    RT = Table.AddColumn(Index, "RT", each List.Sum(List.FirstN(Index[Nums],[Index]))),
    Dif = Table.AddColumn(RT, "Dif", each [RT]-[Nums]+1),
    Combine = Table.AddColumn(Dif, "Custom", each Text.Combine(List.Transform({[Dif]..[RT]},Text.From),",")),
    RC = Table.RemoveColumns(Combine,{"Index", "RT", "Dif"})
in
    RC

 

[Darth Emphatic]

You guys are awesome! Thanks so much!