replace textjoin in Excel 2016 or earlier?

G

Geo Jul

Board Regular
Joined
Nov 19, 2022
Messages
125
Office Version
  1. 2016
Platform
  1. Windows
Dear All
How do I use this formula in Excel 2016 or earlier?
=TEXTJOIN(", ",TRUE,IF(B1:B27<>"",B1:B27,""))
many thanks in advance
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B1&"~"&B2&"~"&B3&"~"&B4&"~"&B5&"~"&B6&"~"&B7&"~"&B8&"~"&B9&"~"&B10&"~"&B11&"~"&B12&"~"&B13&"~"&B14&"~"&B15&"~"&B16&"~"&B17&"~"&B18&"~"&B19&"~"&B20&"~"&B21&"~"&B22&"~"&B23&"~"&B24&"~"&B25&"~"&B26&"~"&B27," ","`"),"~"," "))," ",", "),"`"," ")

Book2
BCDEFGHIJKL
111, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
2abc def
33
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
28
29
Sheet1
Cell Formulas
RangeFormula
E1E1=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B1&"~"&B2&"~"&B3&"~"&B4&"~"&B5&"~"&B6&"~"&B7&"~"&B8&"~"&B9&"~"&B10&"~"&B11&"~"&B12&"~"&B13&"~"&B14&"~"&B15&"~"&B16&"~"&B17&"~"&B18&"~"&B19&"~"&B20&"~"&B21&"~"&B22&"~"&B23&"~"&B24&"~"&B25&"~"&B26&"~"&B27," ","`"),"~"," "))," ",", "),"`"," ")
 
Upvote 0
Scott Huish said:
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B1&"~"&B2&"~"&B3&"~"&B4&"~"&B5&"~"&B6&"~"&B7&"~"&B8&"~"&B9&"~"&B10&"~"&B11&"~"&B12&"~"&B13&"~"&B14&"~"&B15&"~"&B16&"~"&B17&"~"&B18&"~"&B19&"~"&B20&"~"&B21&"~"&B22&"~"&B23&"~"&B24&"~"&B25&"~"&B26&"~"&B27," ","`"),"~"," "))," ",", "),"`"," ")

Book2
BCDEFGHIJKL
111, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
2abc def
33
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
28
29
Sheet1
Cell Formulas
RangeFormula
E1E1=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B1&"~"&B2&"~"&B3&"~"&B4&"~"&B5&"~"&B6&"~"&B7&"~"&B8&"~"&B9&"~"&B10&"~"&B11&"~"&B12&"~"&B13&"~"&B14&"~"&B15&"~"&B16&"~"&B17&"~"&B18&"~"&B19&"~"&B20&"~"&B21&"~"&B22&"~"&B23&"~"&B24&"~"&B25&"~"&B26&"~"&B27," ","`"),"~"," "))," ",", "),"`"," ")
Click to expand...
Thank you for your suggestion
 
Upvote 0
JEC said:
You can use a UDF
Click to expand...
@George Jululian try the UDF Below by @Eric W

VBA Code: 
Function TEXTJOIN2(delimiter As Variant, ignore_empty As Boolean, ParamArray TextArray() As Variant) As String           ' Excel 2019
'
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove
' 2021, Eric W, Added multiple delimiter support
'
    Dim Flag                    As Boolean
    Dim ArrayRow                As Long
    Dim TextArrayRow            As Long, LastRowDelimiterArray  As Long
    Dim DelimiterArray()        As String
    Dim CellToJoin              As Variant
    Dim DelimiterValue          As Variant
'
    TEXTJOIN2 = ""
'
    If TypeOf delimiter Is Range Then
        ReDim DelimiterArray(0 To delimiter.Cells.Count - 1)
        ArrayRow = 0
'
        For Each DelimiterValue In delimiter
            DelimiterArray(ArrayRow) = DelimiterValue.Value
            ArrayRow = ArrayRow + 1
        Next
    ElseIf IsArray(delimiter) Then
''        ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter) + 1)
        ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter))
        ArrayRow = 0
'
        For Each DelimiterValue In delimiter
            DelimiterArray(ArrayRow) = DelimiterValue
            ArrayRow = ArrayRow + 1
        Next
    Else
        ReDim DelimiterArray(0 To 0)
''        DelimiterArray(0) = delimiter
'
        If IsMissing(delimiter) Then
            DelimiterArray(0) = ""
        Else
            DelimiterArray(0) = delimiter
        End If
    End If
'
    ArrayRow = 0
    LastRowDelimiterArray = UBound(DelimiterArray) + 1
    Flag = False
    
    For TextArrayRow = LBound(TextArray) To UBound(TextArray)
        If TypeOf TextArray(TextArrayRow) Is Range Then
            For Each CellToJoin In TextArray(TextArrayRow).Cells
                If CellToJoin = "" And ignore_empty Then
                Else
                    TEXTJOIN2 = TEXTJOIN2 & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin.Value
                    ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                    Flag = True
                End If
            Next
        ElseIf IsArray(TextArray(TextArrayRow)) Then
            For Each CellToJoin In TextArray(TextArrayRow)
                If CellToJoin = "" And ignore_empty Then
                Else
                    TEXTJOIN2 = TEXTJOIN2 & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin
                    ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                    Flag = True
                End If
            Next
        Else
            If TextArray(TextArrayRow) = "" And ignore_empty Then
            Else
                TEXTJOIN2 = TEXTJOIN2 & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & TextArray(TextArrayRow)
                ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                Flag = True
            End If
        End If
    Next
End Function

Book1
BCDE
111, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
2abc def
331, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
Sheet2
Cell Formulas
RangeFormula
E1E1=TEXTJOIN2(", ",TRUE,IF(B1:B27<>"",B1:B27,""))
E3E3=TEXTJOIN(", ",TRUE,IF(B1:B27<>"",B1:B27,""))
 
Upvote 0
For this problem, as a UDF, I would do this:
VBA Code: 
Function TEXTJOIN2(delimiter As Variant, ignore_empty As Boolean, rngValues As Range) As String
Dim c As Range, s As String
For Each c In rngValues
    If c = "" Then
        If Not (ignore_empty) Then s = s & delimiter
    Else
        s = s & delimiter & c
    End If
Next
TEXTJOIN2 = Mid(s, Len(delimiter)+1)
End Function



Book1
ABCDEF
11 1, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
2abc def
33
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
28
Sheet1
Cell Formulas
RangeFormula
F1F1=textjoin2(", ",TRUE,A1:A27)
 
Last edited:
Upvote 0
Upvote 0
JEC said:
How about:
VBA Code: 
 Function jec(r As Range) As String jec = Join(Filter(Evaluate("transpose(if(" & r.Address & "<>""""," & r.Address & "))"), False, 0), ", ") End Function
Book1
ABCD
11
2abc def1, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
33
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
Sheet1
Cell Formulas
RangeFormula
D2D2=jec(A1:A27)
[many
Click to expand...
Cell Formulas
RangeFormula

JEC said:
How about:

VBA Code: 
Function jec(r As Range) As String
jec = Join(Filter(Evaluate("transpose(if(" & r.Address & "<>""""," & r.Address & "))"), False, 0), ", ")
End Function

Book1
ABCD
11
2abc def1, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
33
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
Sheet1
Cell Formulas
RangeFormula
D2D2=jec(A1:A27)
Click to expand...
many thanks for your responses and your soulution
 
Upvote 0
MARK858 said:
@George Jululian try the UDF Below by @Eric W

VBA Code: 
Function TEXTJOIN2(delimiter As Variant, ignore_empty As Boolean, ParamArray TextArray() As Variant) As String           ' Excel 2019
'
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove
' 2021, Eric W, Added multiple delimiter support
'
    Dim Flag                    As Boolean
    Dim ArrayRow                As Long
    Dim TextArrayRow            As Long, LastRowDelimiterArray  As Long
    Dim DelimiterArray()        As String
    Dim CellToJoin              As Variant
    Dim DelimiterValue          As Variant
'
    TEXTJOIN2 = ""
'
    If TypeOf delimiter Is Range Then
        ReDim DelimiterArray(0 To delimiter.Cells.Count - 1)
        ArrayRow = 0
'
        For Each DelimiterValue In delimiter
            DelimiterArray(ArrayRow) = DelimiterValue.Value
            ArrayRow = ArrayRow + 1
        Next
    ElseIf IsArray(delimiter) Then
''        ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter) + 1)
        ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter))
        ArrayRow = 0
'
        For Each DelimiterValue In delimiter
            DelimiterArray(ArrayRow) = DelimiterValue
            ArrayRow = ArrayRow + 1
        Next
    Else
        ReDim DelimiterArray(0 To 0)
''        DelimiterArray(0) = delimiter
'
        If IsMissing(delimiter) Then
            DelimiterArray(0) = ""
        Else
            DelimiterArray(0) = delimiter
        End If
    End If
'
    ArrayRow = 0
    LastRowDelimiterArray = UBound(DelimiterArray) + 1
    Flag = False
   
    For TextArrayRow = LBound(TextArray) To UBound(TextArray)
        If TypeOf TextArray(TextArrayRow) Is Range Then
            For Each CellToJoin In TextArray(TextArrayRow).Cells
                If CellToJoin = "" And ignore_empty Then
                Else
                    TEXTJOIN2 = TEXTJOIN2 & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin.Value
                    ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                    Flag = True
                End If
            Next
        ElseIf IsArray(TextArray(TextArrayRow)) Then
            For Each CellToJoin In TextArray(TextArrayRow)
                If CellToJoin = "" And ignore_empty Then
                Else
                    TEXTJOIN2 = TEXTJOIN2 & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin
                    ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                    Flag = True
                End If
            Next
        Else
            If TextArray(TextArrayRow) = "" And ignore_empty Then
            Else
                TEXTJOIN2 = TEXTJOIN2 & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & TextArray(TextArrayRow)
                ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                Flag = True
            End If
        End If
    Next
End Function

Book1
BCDE
111, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
2abc def
331, abc def, 3, 4, 5, 6, 7, 8, ghi jkl, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27
44
55
66
77
88
9
10
11ghi jkl
12
1313
1414
1515
1616
1717
1818
1919
2020
2121
2222
2323
2424
2525
2626
2727
Sheet2
Cell Formulas
RangeFormula
E1E1=TEXTJOIN2(", ",TRUE,IF(B1:B27<>"",B1:B27,""))
E3E3=TEXTJOIN(", ",TRUE,IF(B1:B27<>"",B1:B27,""))
Click to expand...
many thanks for your responses and your soulution
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
textjoin help
Replies
5
Views
127
Fluff
Fluff
N
  • Question
Remove duplicates of Filter function output.
Replies
1
Views
69
Fluff
Fluff
M
  • Question
Speed-Up efficiency of workbook
Replies
5
Views
175
shift-del
shift-del
D
  • Question
Textjoin
Replies
3
Views
238
Fluff
Fluff
C
  • Solved
VBA: How to reference variables using the TextJoin formula?
Replies
2
Views
203
Coyotex3
C

Forum statistics

Threads
1,216,219
Messages
6,129,575
Members
449,519
Latest member
Rory Calhoun

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top