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

[spiner55]

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

 

[MARK858]

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

 

[Sulprobil]

I suggest to use a similar function:

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