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,""))

 

[mightymo77]

Please keep in mind this is my very first time trying to enter a VBA code into Excel. I'm confused by post # 22 "The code has to be in the workbook that you want to use it in.". What do you mean exactly? Look at my image in post # 29, the code is IN the csv file's VBA window, is it not?

As far as saving the formula and saving it, no it does not matter. Once we run the formula, get the results in column X, we will end up downloading a brand new csv file with updated information the next day.

If you can please guide me through adding in the formula correctly that would be very much appreciated. Please see post # 25 and 29 for the process that I am doing.

 

[JEC]

Open a new excel file, copy the data in there at the same place. Then follow the same steps as before. Does that work?

 

[Fluff]

Look at my image in post # 29, the code is IN the csv file's VBA window, is it not?

Nope, fraid not, It's in your Personal.xlsb file not a csv.
That said as long as your personal.xlsb is always open (which it should be) you can use

=PERSONAL.XLSB!jec(F2:T2,$F$1:$T$1)

 

[mightymo77]

Guys thank you all very much. I got it work. I now see what you meant by "The code has to be in the workbook that you want to use it in". An easier way of saying that would be that the module should be created using the upper set of folders, not the lower set.

Also JEC there was a typo in your code that threw me off - =jec(F2:T2,$F$1:$T$1) After the T2 is a comma, you had a colon.

Thank you very much for your help!

 

[JEC]

Not a typo, just different regional pc settings?

 

[johnnyL]

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

I tested this today and the same limitation of TextJoin, maximum 32,767 character result, exists for the alternative.

 

[Eric W]

That's a limit of Excel. See here:

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

If you have a string longer than that, you'll have to break it up into pieces.

 

[johnnyL]

@Eric W, I was not trying to imply that you did anything wrong, so I hope that you did not infer that. I was just correcting/stating the character limits are the same.

 

[Sulprobil]

Hello,

Why don't you just use a UDF TEXTJOIN like this one:

Option Explicit
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

Just press ALT + F11, insert a new (normal, not class) module, copy above macro code in there, and go back to your spreadsheet.

When you migrate to Excel 2019 or newer you do not need to do anything. Excel will automatically use the built-in function.

Regards,
Bernd

 

[johnnyL]

Hello,

Why don't you just use a UDF TEXTJOIN like this one:

Option Explicit
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

Because that doesn't always work.