TEXTJOIN Alternative

[mightymo77]

Hey guys. I was reading through some posts and everyone here seems very helpful. I have this formula below, however it's not working on my work machine which uses Excel 2016. Can anyone create an alternative for me that will function in Excel 2016? I would appreciate any help!

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND($F2:$T2,"BLACK,BLUE")),$F$1:$T$1,""))

 

[sanantonio]

I would also be interested in a textjoin alternative to overcome the size limitations, which if I remember is 2500 or so.

 

[Eric W]

I wrote up a UDF that imitates TEXTJOIN, look 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

To install it, press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the code into the window that opens. Press Alt-Q to close the editor. Then you should be able to use TEXTJOINX where you would want to use TEXTJOIN. It's been quite a while since I looked at it, but it should work. I don't think I ever tested size limitations, but I'm unaware of any problems.

 

[JEC]

With a UDF: (put this behind a module - > open VBA, insert a new module and paste)

Function jec(searchRng As Range) As String
 Dim it As Variant, c00 As String
  For Each it In searchRng
    If InStr(it, "BLACK,BLUE") Then c00 = c00 & ", " & it.Offset(-1).Value
  Next
  jec = Mid(c00, 3)
End Function

=jec(F2:T2)

 

[mightymo77]

Thank you. I am getting "Compile error: Expected: list separator or )". I never ran a VBA code before. What I did was press ALT + F11, right-click Modules > Insert > Module > Pasted your first code > closed the window and did not save the macro > closed the next window which brought me back to my csv file > pasted the second code in X2 > It brought back the module window with the error.

 

[Fluff]

@JEC you may want to test that function.

 

[JEC]

I actually didn't, what is wrong?

 

[Fluff]

It's checking the wrong way round.

 

[JEC]

It is supposed to textjoin F1:T1 right?

 

[mightymo77]

It is supposed to textjoin F1:T1 right?

Yes that's correct.