How to concatenate multiple cells (only if cell is not blank) with delimiter?

Question

I am trying to concatenate 6 cells with a delimiter of ", " in between each cell, but while ignoring blank cells. I used this question to help me: How to Concatenate multiple columns if not empty.

Problem:

I ran into an issue where the delimiter showed up at the end of the concatenated text. So I used LEFT and LEN to get rid of the extra character. Is there a way to fix this in VBA without using LEFT and LEN in a formula?

VBA Code:

Function Concat(ConcatArea As Range) As String
    For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & ", "): Next
    Concat = Left(xx, Len(xx) - 1)
End Function

Formula:

=LEFT(Concat(Temp[@[En00]:[En05]]),LEN(Concat(Temp[@[En00]:[En05]]))-1)

Solution:

I was able to use a different UDF with the help of @Andreas and Alun Rowe's resource. The UDF seems to emulate the TEXTJOIN function (which is only available in Office 365/2019):

Function IMPLODE(Rng As Range, Sep As String)
    Dim TEMP As String
    For Each Cell In Rng
        If Cell.Value = "" Then
        Else
            TEMP = TEMP & Cell.Value & Sep
        End If
    Next Cell
    TEMP = Left(TEMP, Len(TEMP) - Len(Sep))
    IMPLODE = TEMP
End Function

I'm not sure of the version of excel this was released in, but check to see if you have `TEXTJOIN` function. If not, the hero `Jeeped` has a UDF to do exactly this if you search — urdearboy, Aug 19 '19 at 20:34
Here is the code for a TEXTJOIN() udf if you do not have Office 365 Excel: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell — Scott Craner, Aug 19 '19 at 20:35
WHAT **rip**. He did have a good `TEXTJOIN` udf. I remember trying to recreate it back in the day for practice. — urdearboy, Aug 19 '19 at 20:39
@urdearboy unfortunately Jeeped appears to have deleted their SO account, making their posts rather hard to find now :/ — Mathieu Guindon, Aug 19 '19 at 20:54
Thank you everyone, my work is using Office 2016 so I don't have TEXTJOIN. I was able to modify my VBA with the help of [@Andreas](https://stackoverflow.com/users/5159168/andreas) and [Alun Rowe's resource](https://www.alunr.com/excel-vba-equivalent-of-the-php-implode-function/) . — jeidubyu, Aug 20 '19 at 14:22

score 1 · Accepted Answer · answered Aug 19 '19 at 20:43

1

You can use the PHP equivalent implode function as a UDF.
All credits to the original author here.

Function IMPLODE(Rng As Range, Sep As String)
    Dim TEMP As String
    For Each Cell In Rng
        If Cell.Value = "" Then
        Else
            TEMP = TEMP & Cell.Value & Sep
        End If
    Next Cell
    TEMP = Left(TEMP, Len(TEMP) - Len(Sep))
    IMPLODE = TEMP
End Function

answered Aug 19 '19 at 20:43

Andreas

23,610
6
30
62

Thank you very much for sharing an excellent resource and providing an answer. This worked perfectly for me. – jeidubyu Aug 20 '19 at 14:17

How to concatenate multiple cells (only if cell is not blank) with delimiter?

1 Answers1