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!

 

[spacely]

I tried to adapt yours to get the range from another sheet... it's not working this way:

Sub OneNumberPerCellDownward()
Dim Combined() As String
Combined = Split(Join(Application.Transpose(Worksheets("CONFIGURATIONS").Range("Y2", Cells(Rows.Count, "Y").End(xlUp)), ","), ","))
Range("AC2").Resize(1 + UBound(Combined)) = Application.Transpose(Combined)
End Sub

 

[Peter_SSs]

I think I reached a limit ..

Yes, with that many numbers we run into a problem when the string exceeds 32,767 characters and that will happen when the substitute takes place and you have a lot of numbers like that.

Never-the-less, I suspect we can work our way around that pretty easily. Given that all your numbers appear to be exactly 4 digits (though some only have a comma separator and some have comma-space separator), we should be able to use one of the following ..

A) If you are happy for the result to be a string (eg "4001") rather than a number (4001) then try

=MID(SUBSTITUTE(TEXTJOIN(",",TRUE,A$2:A$42)," ",""),ROWS(B$2:B2)*5-4,4)

[If you didn't have any comma-spaces it would just be ..]
=MID(TEXTJOIN(",",TRUE,A$2:A$42),ROWS(B$2:B2)*5-4,4)


B) If you want the result as a number then try

=IFERROR(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,A$2:A$42)," ",""),ROWS(B$2:B2)*5-4,4)+0,"")

[If you didn't have any comma-spaces it would just be]
=IFERROR(MID(TEXTJOIN(",",TRUE,A$2:A$42),ROWS(B$2:B2)*5-4,4)+0,"")


If you actually don't have all 4-digit numbers then post back for another option.

 

[Rick Rothstein]

I tried to adapt yours to get the range from another sheet... it's not working this way:

[table="width: 500"]
[tr]
	[td]Sub OneNumberPerCellDownward()
  Dim Combined() As String
  Combined = Split(Join(Application.Transpose(Worksheets("CONFIGURATIONS").Range("Y2", [B][COLOR="#FF0000"]Worksheets("CONFIGURATIONS").[/COLOR][/B]Cells(Rows.Count, "Y").End(xlUp)), ","), ","))
  Range("AC2").Resize(1 + UBound(Combined)) = Application.Transpose(Combined)
End Sub[/td]
[/tr]
[/table]

You missed qualifying one of the ranges (I added it in red above).


Edit Note
-------------------
One thing that should be mentioned... when you put your data into the cells, it is important that the cells be formatted as Text... if you don't do that, a value like this from your example data...

4016,4040,4045

will end up being converted to the real number...

401640404045

and be displayed in the cell like this...

401,640,404,045

 

[spacely]

Hi Rick, that line now gives "invalid number of arguments"...

Combined = Split(Join(Application.Transpose(Worksheets("CONFIGURATIONS").Range("Y2", Worksheets("CONFIGURATIONS").Cells(Rows.Count, "Y").End(xlUp)), ","), ","))

...maybe I missed a parenthesis...

(Yea, I stumbled on the Text thing, and used a ' at the start of each line to stop excel from interpreting as a huge number... and removed spaces)

 

[spacely]

Well, the 4 digit number only solution is working nicely. But, we have had in the past 3-digit numbers, and for the sake of robustness, I gotta ask, you have something even better?

in the end these populate the dropdown of a combo widget.

 

[Peter_SSs]

Well, the 4 digit number only solution is working nicely. But, we have had in the past 3-digit numbers, and for the sake of robustness, I gotta ask, you have something even better?

Would have been helpful to clarify some of the issues I raised earlier.

Since that didn't happen, this assumes that
- text results (not numbers) is fine and
- there are no comma-space delimiters in the data.

Smaller example but numbers of any length.

Excel Workbook
	A	B
1	Data	Single Numbers
2	445	445
3	333,678456,1.00023,2,304	333
4	21,34	678456
5		1.00023
6	600	2
7	345,1234567891011121314151617,20	304
8		21
9		34
10		600
11		345
12		1234567891011121314151617
13		20
14
Single column

 

[Rick Rothstein]

Hi Rick, that line now gives "invalid number of arguments"...

Combined = Split(Join(Application.Transpose(Worksheets("CONFIGURATIONS").Range("Y2", Worksheets("CONFIGURATIONS").Cells(Rows.Count, "Y").End(xlUp)), ","), ","))

...maybe I missed a parenthesis...

There was a parenthesis in the wrong place. The following line should now work...

[table="width: 500"]
[tr]
	[td]Combined = Split(Join(Application.Transpose(Worksheets("CONFIGURATIONS").Range("Y2", Worksheets("CONFIGURATIONS").Cells(Rows.Count, "Y").End(xlUp))), ","), ",")[/td]
[/tr]
[/table]

 

[spacely]

Yep. Works! Nice thing about VBA is I can hook other Widget updates to it. But it's manual.

 

[spacely]

Would rather have the numerical answers. Maybe i can add 0 somewhere...

That actually doesn't work on my big long list. It messes up right after 4097 (exactly the 200th number).

 

[Rick Rothstein]

Yep. Works! Nice thing about VBA is I can hook other Widget updates to it. But it's manual.

I can make my macro into event code so that whenever a value in Column Y is changed, the list of single numbers would be automatically repopulated. How well that would work might depend on how the data gets into Column Y. Given you comment earlier about putting an apostrophe in front of comma delimited numbers that can be interpreted as long normal numbers, it almost sounds like you enter the numbers manually. Can you describe briefly how the data gets into Column Y for us?

Would rather have the numerical answers. Maybe i can add 0 somewhere...

That actually doesn't work on my big long list. It messes up right after 4097 (exactly the 200th number).

When there are multiple responders to your question, you should indicate who you are responding to when you reply back to a posted message. Who was the above comment directed to... me or Peter?