Need robust replacement for TEXTJOIN function to run under Excel 2016.

[CharlieNVA]

I have seen recommendations to write user defined functions to replace TEXTJOIN

I have tried Confusion Of Join Method written by Sulprobil which works for simple ranges but fails when the input parameters are formulas.

Function TEXTJOIN(Delimiter As String, _
Ignore_empty As Boolean, _
ParamArray Text() As Variant) As String
Dim v, i As Long, s As String
For i = LBound(Text) To UBound(Text)
For Each v In Text(i)
If Not (Ignore_empty And v = "") Then
TEXTJOIN = TEXTJOIN & s & v
s = Delimiter
End If
Next v
Next i
End Function


=TEXTJOIN("|", TRUE, $A5, $C5:$H5,TEXT($I5,"mm/dd/yyyy"),IF(ISBLANK($J5),"",TEXT($J5,"mm/dd/yyyy") )

where A5, c5:h5 are text and I5 and j5 are date fields

It can't seem to handle the TEXT($I5,"mm/dd/yyyy") or if formulas.

Has anyone seen some sample code that can handle this?

I am porting someone else's code from an office 365 environment to office 2106. Note the sample I provided is simplified. The actual formula joins more than 100 columns that repeat the if(isblanks ... pattern.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Sulprobil]

Try this Version. Does it work for you?:

Option Explicit
Function TEXTJOIN(Delimiter As String, _
Ignore_empty As Boolean, _
ParamArray Text() As Variant) As String
'07-Jan-2022 PB V1.1
Dim v, i As Long, s As String, t As String
For i = LBound(Text) To UBound(Text)
If IsArray(Text(i)) Then
For Each v In Text(i)
t = IIf(IsMissing(v), "", v)
If Not (Ignore_empty And t = "") Then
                TEXTJOIN = TEXTJOIN & s & t
                s = Delimiter
End If
Next v
Else
t = IIf(IsMissing(Text(i)), "", Text(i))
If Not (Ignore_empty And t = "") Then
            TEXTJOIN = TEXTJOIN & s & t
            s = Delimiter
End If
End If
Next i
End Function

 

[CharlieNVA]

This did not solve it. I will post more on my experimental runs later this afternoon. As a side note: IFF is also incompatible with excel version 2016 which I am forced to used for this project. I replaced IFF with IF else equivalent but it still failed.

 

[Fluff]

with excel version 2016 which I am forced

Please update your profile as requested.
Also IIF works quite happily in 2016

 

[Eric W]

I wrote up a UDF to emulate TEXTJOIN. See here:

ARRAY FORMULA (TEXTJOIN) periodically not updating or returning correct values

Hi, I am using a TEXTJOIN function in an ARRAY FORMULA to return multiple matches to one cell. However periodically the formula doesnt update and/or doesnt update correctly. See formula in the attached - cell reference - D63 If you play with the inputs in cells L37:L39 - you will see that...

www.mrexcel.com

See post 4 for the original version. This should work for most situations. Then the OP said that TEXTJOIN has some interesting options for the delimiters, which prompted me to update the UDF to handle those cases. The updated version is in post 11. Let me know how this works for you.

 

[CharlieNVA]

Eric W's code did the trick. Thank you all.

 

[Sulprobil]

Hello Charlie,

Would you mind to provide an explicit example of my code not working?
I checked some of your cases on my own but I could not find any issue.
Thank you.

Regards,
Bernd