# TEXTJOIN Formula Is Incompatible with MS Excel 2016: Need Alternate Formula(s)

#### spiner55

##### New Member
Hello,

I was running a worksheet in office 365 but now only have access to Excel 2016. The TEXTJOIN formula is incompatible with Excel 2016.

The formula I was using was ="• "&_xlfn.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.

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

Can this still be done without the TEXTJOIN Function?

Here is a link to the video explaining the formula in more depth. How to create risk map in Excel - Charting Tip

Here is a link to a downloadable file. Free Excel Risk Map Template » Project Management » Chandoo.org

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### MARK858

##### MrExcel MVP
There is no formula alternative to TEXTJOIN in 2016, the best that you can do is use a UDF.

#### Sulprobil

##### Board Regular
I suggest to use a similar function:
VBA Code:
``````Enum mc_Macro_Categories
mcFinancial = 1
mcDate_and_Time
mcMath_and_Trig
mcStatistical
mcLookup_and_Reference
mcDatabase
mcText
mcLogical
mcInformation
mcCommands
mcCustomizing
mcMacro_Control
mcDDE_External
mcUser_Defined
mcFirst_custom_category
mcSecond_custom_category 'and so on
End Enum 'mc_Macro_Categories

Function sbCat(vP As Variant, _
Optional sDel As String = ",", _
Optional bNonEmpty As Boolean = True) As String
'Concatenate all cells in a range or array, delimited
'by sDel. If bNonEmpty is TRUE then only non-empty
'cells will be concatenated.
'Reverse ("moc.LiborPlus.www") PB V1.0 04-Oct-2010
Dim v, s As String

For Each v In vP
If Not (bNonEmpty And v = "") Then
sbCat = sbCat & s & v
s = sDel
End If
Next v

End Function

Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 3) As String

FuncName = "sbCat"
FuncDesc = "Concatenates all elements of a range or array vP " & _
"with delimiter sDel. If bNonEmpty is True then only " & _
"non-empty cells will be concatenated"
Category = mcText
ArgDesc(1) = "Range or array with elements to be concatenated"
ArgDesc(2) = "Delimiter [Optional, pre-set as ',']"
ArgDesc(3) = "Boolean value [Optional], True: concatenate only non-empty elements, " & _
"False: concatenate all elements"

Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub``````

Article sbCat

Replies
3
Views
630