Need TextJoin Alternative for MS Excel 2016

Hello Everyone,

So since The TEXTJOIN formula is incompatible with Excel 2016, I was wondering if there was an alternative formula or VBA code.

The formula I was using was {="• "&TEXTJOIN(CHAR(10)&"• ",TRUE,IF(risks[Likelihood]=$C3,IF(risks[Impact]=D$8,risks[Title],""),""))}

This formula plots the name of the risk on a chart depending on it's probability score and impact score. See attached for the visual. Textjoin would be

The formula allows for multiple risks to be inputted in a single cell.

Can this still be done without the TEXTJOIN Function?

risk-map (2).xlsx

Custom function, is it the result you want

As others have mentioned, VBA user-defined functions are your only option. However, one caveat: Excel 2019 and later TEXTJOIN supports 3D ranges, that is,

=TEXTJOIN(", ",1,Sheet1:Sheet4!B5:G10)

iterates through the 3D block in the same order that NPV iterates through 3D blocks as its 2nd argument. VBA still can't handle 3D references. Probably NBD.

Please Login or Register  to view this content.

Originally Posted by MrShorty

Are you required to use Excel? LO calc, Gnumeric, and Google sheets all support the TEXTJOIN() function without hiding the function behind a subscription wall. Often, when I want one of the newer functions that my older version of Excel doesn't support, I see if the function is available in open source spreadsheets and use another spreadsheet.

The TEXTJOIN() function is essentially the same as the VBA Join() function. It should be possible to write a VBA UDF that will provide the same functionality, but it will not automatically "plug and play" into existing spreadsheets. You will need to do some kind of find/replace to use your UDF in files that you receive from others, and they will need some kind of find replace to replace your UDF with the TEXTJOIN() function in their version. (In short, this makes it harder to share your files with others if these are projects that need to pass around between other team members).

Those are the two main approaches I use to get the same functionality as the TEXTJOIN() function. The best approach may depend on details that you have not shared with us.

Thanks for the reply. I'll admit, I did not consider using google sheets or other free spreadsheet options to get around the different versions with their varying features. It would be difficult however because this one massive workbook that this probability and impact matrix is located in contains many other sheets so the formatting and maybe some of the other formulas wouldn't transfer over, so it would take some work. Although that's a good idea to keep in mind, I should probably consider making a google sheet version of the workbook to give people who don't have an office 365 subscription another option.

Originally Posted by wk9128

Custom function, is it the result you want

This works great! The only thing I would add is another bullet point to cover the first risk added to the matrix. Thank you!!

Originally Posted by Bo_Ry

Please try at
Data G6
="• "&C6&IFNA(CHAR(10)&"• "&INDEX(C7:C$46,MATCH(D6*10+E6,INDEX(D7:D$46*10+E7:E$46,),)),"")

Rename column header G5 to Join

Risk Map D3:H7
=IFNA(INDEX(risks[Join],MATCH(D$8*10+$C3,INDEX(risks[Impact]*10+risks[Likelihood],),)),"")

This works perfectly, and without any vba. This works for risk probabilities and impacts categorized as numbers 1-5, but it wouldn't be difficult at all to take subjective ratings (i.e. "Very Low", "Medium", "High", etc.) and assign corresponding values if needed. Thank you kindly

Originally Posted by BMV

Please Login or Register  to view this content.

This is another great option for a UDF that mimics TextJoin! Works great You're a genius. Thank you.

ОБЪЕДИНИТЬ = Combine

Why both ARG and ARG1, and ARG undeclared?

Why the Application.Transpose call in all cases? It's necessary for arrays but not Ranges since VBA, in MSFT's infinite wisdom, iterates differently through SomeRange.Cells and SomeRange.Value.

Why the overhead of a subfunction call? If you always use RESULT = RESULT & Delimiter & CStr(X), then all you need at the end is TEXTJOIN = Mid$(RESULT, Len(Delimiter) + 1). Much more efficient than an unnecessary function call for every X.

Finally, while VBA doesn't support 3D references, it does support multiple area ranges. Overengineered, but a closer alternative,

Please Login or Register  to view this content.

hrlngrv

Please Login or Register  to view this content.

will work but any operation with range a little bit slow then with array.

Fair point. Handle multiple-area ranges separately, and single area ranges by converting to their .Value arrays.

Please Login or Register  to view this content.

Then use a profiling macro.

Please Login or Register  to view this content.

Yes, processing single-area ranges is slower than processing arrays, and processing multiple-area ranges is a lot slower still. However, if a TEXTJOIN replacement needs to function as close to the actual TEXTJOIN in Excel 2019 and later, isn't slow preferable to impossible?

Given the profiling macro above, on my system processing multiple-area ranges takes 140% more time than 1D arrays, processing single-area ranges takes 40% more time than 1D arrays, and processing 2D arrays takes 11% more time than 1D arrays (I supplemented the macro above for 2D arrays).

Note also that my profiling macro performs 500,000 iterations. Each For loop takes under 30 seconds, so each iteration, thus textjoin call, takes on the order of tens of microseconds. Given the speed of individual calls, my own preferences is to handle multiple-area ranges. That's subjective; others may have different preferences.

Thank you so much for your idea!!

I´m trying to make it work but I´m not able....it only detects the lines that are under each and only, if you have three risks with the same value, only detects the first two and I´m not able to identify why!! Could you please have a look?

Actually I cannot use vba code in our sheets, so I´m looking at this kind of solution.
Regards!!

Originally Posted by Bo_Ry

Please try at
Data G6
="• "&C6&IFNA(CHAR(10)&"• "&INDEX(C7:C$46,MATCH(D6*10+E6,INDEX(D7:D$46*10+E7:E$46,),)),"")

Rename column header G5 to Join

Risk Map D3:H7
=IFNA(INDEX(risks[Join],MATCH(D$8*10+$C3,INDEX(risks[Impact]*10+risks[Likelihood],),)),"")

Thank you so much for your idea!!

I´m trying to make it work but I´m not able....it only detects the lines that are under each and only, if you have three risks with the same value, only detects the first two and I´m not able to identify why!! Could you please have a look?

Actually I cannot use vba code in our sheets, so I´m looking at this kind of solution.
Regards!!