Substitution for TEXTJOIN

[shahdelsol]

I am using the new Excel formula " TEXTJOIN" and works great however this formula only works on the new version of office 360. What formula can I replace this with on older version of Excel? Does anyone know? Thanks in advance.


{=TEXTJOIN(",",0,IF(J20:J23>0,H20:H23,""))}

 

[FormR]

Hi, if you are only concatenating a few cells an alternative would be:

=MID(IF(J20>0,","&H20,"")&IF(J21>0,","&H21,"")&IF(J22>0,","&H22,"")&IF(J23>0,","&H23,""),2,999)

As you can see though, this quickly gets unmanageable as the number of cells increases - the other alternative is to use a custom user-defined function.

 

[Aladin Akyurek]

have a look at the ACONCAT solutions posted in this forum.

 

[shahdelsol]

Thanks for your solution. Yes it does get unmanageable and I do have about 14 cells to do this for but sounds like I may not have any better option.

 

[shahdelsol]

I wasn't able to find anything. Is there a specific way to do search here?

 

[FormR]

I wasn't able to find anything. Is there a specific way to do search here?

Here is the code:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant


    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If


    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

To use it,
1. With your workbook open press ALT+F11 to open the VBE
2. Click Insert > Module
3. Copy the code above into the code window, top right.
4. Close the VBE
5. Save your workbook as a macro enabled workbook (.xlsm)

One implementation of the formula would then be:

=MID(aconcat(IF(J20:J23>0,", "&H20:H23,"")),3,999)

Which is an array formula and needs confirming with CTRL+SHIFT+ENTER.

 

[shahdelsol]

Thank you so much. This is working great!

 

[shahdelsol]

Thank you so much. This works great.

Here is the code:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant


    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If


    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

To use it,
1. With your workbook open press ALT+F11 to open the VBE
2. Click Insert > Module
3. Copy the code above into the code window, top right.
4. Close the VBE
5. Save your workbook as a macro enabled workbook (.xlsm)

One implementation of the formula would then be:

=MID(aconcat(IF(J20:J23>0,", "&H20:H23,"")),3,999)

Which is an array formula and needs confirming with CTRL+SHIFT+ENTER.

 

[KellyWills]

Hi, if you are only concatenating a few cells an alternative would be:

=MID(IF(J20>0,","&H20,"")&IF(J21>0,","&H21,"")&IF(J22>0,","&H22,"")&IF(J23>0,","&H23,""),2,999)

As you can see though, this quickly gets unmanageable as the number of cells increases - the other alternative is to use a custom user-defined function.

Hi, this thread is been awhile but may I know how to make this formula suits mine?

=TEXTJOIN(", ",1,FILTER(Tags!$A$2:$A$12,ISNUMBER(SEARCH(" "& Tags!$B$2:$B$12&" "," "&R2&" "&S2&" ")),""))

 

[Peter_SSs]

Hi, this thread is been awhile but may I know how to make this formula suits mine?

=TEXTJOIN(", ",1,FILTER(Tags!$A$2:$A$12,ISNUMBER(SEARCH(" "& Tags!$B$2:$B$12&" "," "&R2&" "&S2&" ")),""))

This is a duplicate to the conversation going on in this thread: How to use keyword to tag every row automatically

In future, please do not post the same question multiple times. See Forum Rules (#12).
Please just continue in the existing thread.