Multiple values in one cell separated by commas

[tsm1111]

I have a huge table (over 400 columns, with up to 200 rows of data) that is composed of columns that have different number of rows with values (Column A contains data in rows 1-59, column B in rows 1-129). I want to create cells in Column A -> Z that combine values from rows non empty rowes separated by commas, but only if the value exists, so I do not get multiple commas when joining cells that are empty when using formula =A1&","&A2&","......Any ideas?

 

[Joe4]

Welcome to the Board!

One simple way that does not involve and VBA is to save the file as a CSV file (but give it another extension, like ".txt"). Then re-open the file in Excel, which should invoke the Text Import Wizard. Choose Delimiter, but do NOT choose comma as your separator, choose something else that you would never use (maybe the pipe sumbol). Now all the data should be in column A with commas separating them.

Do get rid of the multiple commas, use Search and Replace, replacing two comams with a single comma. You may need to do this part more than once.

If you are looking for an automated solution, let us know and we can come up with some VBA code to do this.

 

[tsm1111]

Thank you for the reply. I need this to be an automatic update based on another worksheet. I was looking for a sort of a formula that uses something like CONCATENATE, but with and if statement (not sure that it is even possibble) that would prevent adding empty cells. and producing results like a,b,c,d instead of a,b,c,d,,,,,,,,,,,,,

 

[Joe4]

Sure, we can create a custom User Defined Function (UDF) to do that, i.e.

Function MyConcat(myRange As Range) As String
 
    Dim cell As Range
    Dim myString As String
    
'   Concatenate all non-blank values
    For Each cell In myRange
        If Len(cell) > 0 Then myString = myString & cell.Value & ","
    Next cell
    
'   Remove last comma from end
    If Len(myString) > 0 Then MyConcat = Left(myString, Len(myString) - 1)
        
End Function

This UDF can then be used in any Macro, or directly on the worksheet like any other formula, i.e.
=MyConcat(A1:G1)

 

[tsm1111]

I have never created UDF. So most likely doing something wrong, a little bit more help please. I went to VBA (Alt+F11), insert -> module, and copied the code you provided. When I use this formula =MyConcat(A1:A2), I am getting an error: #NAME?

when I select only two cells A1 and A2 (cell values A1=a, A2=b) Function Argument box says ={"a";"b"}
Undifiend
Formula result =

 

[Rick Rothstein]

I have never created UDF. So most likely doing something wrong, a little bit more help please. I went to VBA (Alt+F11), insert -> module, and copied the code you provided. When I use this formula =MyConcat(A1:A2), I am getting an error: #NAME?

Are you sure you selected a "Module" (what you want), not a "Class Module" (what you do not want)?

when I select only two cells A1 and A2 (cell values A1=a, A2=b) Function Argument box says ={"a";"b"}
Undifiend
Formula result =

I'm not sure what you mean by this... what exactly do you mean "when I select only two cells"? Select them when?

 

[Joe4]

Also, check to make sure that you have macros/VBA enabled.

 

[tsm1111]

Thank you so much!! I tried inserting the module again and it worked this time. Would it be aslo possible to modify this code so that the values are in separate lanes in the same cell, just like using alt+return, so instead of a,b I would get:
a
b

Again than you for your help!

 

[Rick Rothstein]

Thank you so much!! I tried inserting the module again and it worked this time. Would it be aslo possible to modify this code so that the values are in separate lanes in the same cell, just like using alt+return, so instead of a,b I would get:
a
b

Change this line of code in Joe's macro...

        If Len(cell) > 0 Then myString = myString & cell.Value & ","

to this...

        If Len(cell) > 0 Then myString = myString & cell.Value & vbLf

You might have to manually enable word wrap for the cells (Alignment tab in the Format Cells dialog box) to show the multiple lines.

 

[tsm1111]

is it not possible to have UDF in xlsx file? I am getting the folowing error when trying to save the module

The following feature cannot be saved in macro-free workbooks

VB project

To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list

Also the following is producing an empty cell:

Function MyConcat(myRange As Range) As String

Dim cell As Range
Dim myString As String

' Concatenate all non-blank values
For Each cell In myRange
If Len(cell) > 0 Then myString = myString & cell.Value & vbLf
Next cell

End Function

Please avise!!