Concatenate If Not Null List With Commas

Question

In Excel, I need to Concatenate every other cell into one "master" cell. I have used this formula to conquer before =SUBSTITUTE(TRIM(G2 & " " & BC2 & " " & BE2 & " " & BG2), " ", ", ") but this alters the data when the data I am concatenate has a comma in it.

The range of cells that I need to concatenate is every other cell ranging from G2 all the way to BG2. What would be the best course of action to make this happen handling concatenation that involves comma lists?

EDIT
By what I mean with alters data is that this

S223 - Pills, S2323 - Patterns - Backstock, 1/Var

becomes this with the formula above

S223, -, Pills,, S2323, -, Patterns, -, Backstock,, 1/Var

TEXTJOIN(), it is available on office 365 excel. It has the ability to ignore blank cells. — Scott Craner, Jul 21 '17 at 15:24
What do you mean "Alters the data when I have a comma in it". This formula concatenates all of the values, trims beginning and ending whitespace, and then swaps out any spaces with a comma and a space. It doesn't alter existing spaces in the data. I suspect you might want a `SUBSTITUTE(TRIM(....), ",", ";")` inside your existing Substitute function to swap commas in the data with a semicolon or something...? — JNevill, Jul 21 '17 at 15:24
@ScottCraner - it looks like the Textjoin() function would work, but is this avaliable in Excel 2013? I am unable to locate it in the functions, and nothing pops up when i start to type =TEXTJO — BellHopByDayAmetuerCoderByNigh, Jul 21 '17 at 15:29
Instead of substitute, you can just concatenate with a comma instead of a space (or use textjoin so you don't have to monkey with it): `=TRIM(G2 & ", " & BC2 & ", " & ..)` — JNevill, Jul 21 '17 at 15:31
No, but you can use the UDF I made here: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell that mimics the TEXTJOIN. — Scott Craner, Jul 21 '17 at 15:32
@JNevill - that does not work if the cell is empty. Then I end up with random comma's at the end. Textjoin does not seem to be avaliable in Excel 2013 — BellHopByDayAmetuerCoderByNigh, Jul 21 '17 at 15:32
@ScottCraner - using the UDF iif I seperate my cells like this G2 & " " & I2 or like this G2, I2 .... I get an error of #Value! — BellHopByDayAmetuerCoderByNigh, Jul 21 '17 at 15:38

score 1 · Accepted Answer · answered Jul 21 '17 at 15:41

1

Use the UDF on: VLOOKUP with multiple criteria returning values in one cell

The formula:

 =TEXTJOIN(", ",TRUE,IF(MOD(COLUMN(G2:BG2),2)=1,G2:BG2,""))

Being an array formula it would need to be confirmed with Ctrl-Shift-Enter.

answered Jul 21 '17 at 15:41

Scott Craner

148,073
10
49
81

That got it perfectly! Thank you!!! Excel actually picked it up as an array formula so I ddi not have to use Ctrl+Shift+Enter – BellHopByDayAmetuerCoderByNigh Jul 21 '17 at 15:42

MisterBic · Answer 2 · 2017-07-21T15:42:45.407

0

Quite some while ago, i found this code online as i needed it for a personal project of mine. What this does is it takes a string and replace every "unallowed" value by a specified character. In your case I would imagine you allowing every character except "," and replace it by "" or " ". This way, A, -, B, -, C, would become A - B - C

Function cleanString(text As String) As String

    Dim output As String
    Dim c
    For i = 1 To Len(text)
        c = Mid(text, i, 1)
        If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z" Or c = " " Or c = "-" Or c = "é" Or c = "É" Or c = "_") Then ' <=list of allowed values in a string
            output = output & c
        Else
            output = output & " " '<= what unallowed values gets replaced by
        End If
    Next
    cleanString = output
End Function

Hope this can help, I considered VBA as you added the tag in your question.

edited Jul 21 '17 at 15:42

answered Jul 21 '17 at 15:38

MisterBic

307
5
18

My solution propose to "clean" the string AFTER using your formula instead of finding a better sutied formula wich seems pretty hard by looking at all the comments. – MisterBic Jul 21 '17 at 15:40
How would I utilize this function? I know to place in module but after such? – BellHopByDayAmetuerCoderByNigh Jul 21 '17 at 15:41
Basically, I imagine you would design some sort of onChangeEvent in your workbook that calls this sub where the input string is the value of your cell – MisterBic Jul 21 '17 at 15:42

score 0 · Answer 3 · answered Jul 21 '17 at 15:39

The following UDF from answers.microsoft.com should be what you're after

Function TEXTJOIN(delimiter As String, ignore_empty As String, ParamArray textn() As Variant) As String
    Dim i As Long
    For i = LBound(textn) To UBound(textn) - 1
        If Len(textn(i)) = 0 Then
            If Not ignore_empty = True Then
                TEXTJOIN = TEXTJOIN & textn(i) & delimiter
            End If
        Else
            TEXTJOIN = TEXTJOIN & textn(i) & delimiter
        End If
    Next
    TEXTJOIN = TEXTJOIN & textn(UBound(textn))
End Function

Concatenate If Not Null List With Commas

3 Answers3