delimit multiple rows' lists, collect into a column...

[spacely]

So, I have a column with rows like:

445
333,678,23
21,34
6
345,20

...and I need to delimit all those with the comma, and make a single column somewhere else like:

445
333
678
23
21
34
6
345
20


Rather not turn to VBA...probably an array function where you shift+ctrl+enter ?

Thanks!

 

[sandy666]

try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"src", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"src", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "src"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"src", Int64.Type}})
in
    #"Changed Type1"[/SIZE]

 

[spacely]

What is this? VBA?

 

[Rick Rothstein]

If you should change your mind about using VBA, here is a macro that will look for the comma delimited numbers in Column A and output the individual numbers in Column B...

[table="width: 500"]
[tr]
	[td]Sub OneNumberPerCellDownward()
  Dim Combined() As String
  Combined = Split(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ","), ",")
  Range("B1").Resize(1 + UBound(Combined)) = Application.Transpose(Combined)
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (OneNumberPerCellDownward) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[sandy666]

What is this? VBA?

No.
This is PowerQuery M language

 

[Peter_SSs]

Rather not turn to VBA

If you have the TEXTJOIN function (Office 365), try this, copied down.

Excel Workbook
	A	B
1	Data	Single Numbers
2	445	445
3	333,678,23	333
4	21,34	678
5	6	23
6	345,20	21
7		34
8		6
9		345
10		20
11
Single column

 

[spacely]

Peter, This seems like a good solution that is working. Looking at the hardcoded values, I have to ask are there total-number-of-line limits, or character limits?

 

[spacely]

Well, spoke too soon. I think I reached a limit since I see some spaces and strange single digit numbers in my actual sample. It doesn't paste right because it's wrapping here. The 1st line end is at 4102.:

4001,4002,4003,8015,8019,4007,4009,8045,8047,8053,8055,8072,8074,2324,2330,8078,8087,8024,8089,8091,4119,4120,4048,4050,4054,4056,4058,4060,4062,4064,4066,4068,4082, 4084, 4101, 4102
4004,4005,4008,4013,8046,8048,8054,8056,8073,8075,2325,2329,8079,4035,8084,8085,8086,8088,4047,4049,4053,4055,4057,4059,4061,4063,4065,4067,4081, 4083, 4099,4100
4125
4105, 4117, 4128, 4130
4106, 4118, 4129, 4131
4157
4158
2301,2302,2304,2305,2308,2309,2313,2315,8064,8066,8068,2321, 4136, 4138, 4140
2303,2306,2307,2310,2311,2314,2317,8065,8067,8069,2322,4137,4139,4141
4006,4011,4012,8070,2326,2328,8042,8090,8092, 4107, 4121,4051,4052,4069,4070,4072,4074,4076,4086,4089,4090,4093,4094, 4113,4112,4122,4123
4010,4014,8071,2323,2327,8083,4103,4071,4073,4075,4077, 4078, 4079, 4080, 4085,4087,4088,4091,4092,4114,4115
8018,2316,2319
2312,2318,2320
4017
4018
4015,4046
4016,4040,4045
4036
4037
4041
4039
4038
4032
4027
4019,4025,4108, 4109
4020,4030,4110,4111
4021,4033
4022,4028,4104
4023,4026
4024,4031
4029,4044
4034,4043
4095, 4096
4097, 4098
4116
4127
4132,4133,4142,4143,4148,4150,4155
4134,4135,4144,4145,4149,4151,4156
4152,8094
4153,8093
4154,8095

 

[Rick Rothstein]

You should give the code I posted in Message #4 a try.

 

[spacely]

I could, but then I need to activate the code somehow, or with a button. I'd rather not use events or clutter with buttons. It should be automatic to the user.

His "function" just messes up exactly every 25 numbers with an error....