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

spiner55

New Member
Joined
Oct 1, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

1601576576826.png
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,458
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
There is no formula alternative to TEXTJOIN in 2016, the best that you can do is use a UDF.
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
118
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,261
Members
410,905
Latest member
Extjel
Top