Book2 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | |||
1 | 1 | 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 | |||||||||||
2 | abc def | ||||||||||||
3 | 3 | ||||||||||||
4 | 4 | ||||||||||||
5 | 5 | ||||||||||||
6 | 6 | ||||||||||||
7 | 7 | ||||||||||||
8 | 8 | ||||||||||||
9 | |||||||||||||
10 | |||||||||||||
11 | ghi jkl | ||||||||||||
12 | |||||||||||||
13 | 13 | ||||||||||||
14 | 14 | ||||||||||||
15 | 15 | ||||||||||||
16 | 16 | ||||||||||||
17 | 17 | ||||||||||||
18 | 18 | ||||||||||||
19 | 19 | ||||||||||||
20 | 20 | ||||||||||||
21 | 21 | ||||||||||||
22 | 22 | ||||||||||||
23 | 23 | ||||||||||||
24 | 24 | ||||||||||||
25 | 25 | ||||||||||||
26 | 26 | ||||||||||||
27 | 27 | ||||||||||||
28 | |||||||||||||
29 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1 | E1 | =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," ","`"),"~"," "))," ",", "),"`"," ") |
Thank you for your suggestion=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
B C D E F G H I J K L 1 1 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 2 abc def 3 3 4 4 5 5 6 6 7 7 8 8 9 10 11 ghi jkl 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25 25 26 26 27 27 28 29 Sheet1
Cell Formulas Range Formula E1 E1 =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," ","`"),"~"," "))," ",", "),"`"," ")
@George Jululian try the UDF Below by @Eric WYou can use a UDF
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 | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
1 | 1 | 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 | ||||
2 | abc def | |||||
3 | 3 | 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 | ||||
4 | 4 | |||||
5 | 5 | |||||
6 | 6 | |||||
7 | 7 | |||||
8 | 8 | |||||
9 | ||||||
10 | ||||||
11 | ghi jkl | |||||
12 | ||||||
13 | 13 | |||||
14 | 14 | |||||
15 | 15 | |||||
16 | 16 | |||||
17 | 17 | |||||
18 | 18 | |||||
19 | 19 | |||||
20 | 20 | |||||
21 | 21 | |||||
22 | 22 | |||||
23 | 23 | |||||
24 | 24 | |||||
25 | 25 | |||||
26 | 26 | |||||
27 | 27 | |||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1 | E1 | =TEXTJOIN2(", ",TRUE,IF(B1:B27<>"",B1:B27,"")) |
E3 | E3 | =TEXTJOIN(", ",TRUE,IF(B1:B27<>"",B1:B27,"")) |
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 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | 1 | 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 | ||||||
2 | abc def | |||||||
3 | 3 | |||||||
4 | 4 | |||||||
5 | 5 | |||||||
6 | 6 | |||||||
7 | 7 | |||||||
8 | 8 | |||||||
9 | ||||||||
10 | ||||||||
11 | ghi jkl | |||||||
12 | ||||||||
13 | 13 | |||||||
14 | 14 | |||||||
15 | 15 | |||||||
16 | 16 | |||||||
17 | 17 | |||||||
18 | 18 | |||||||
19 | 19 | |||||||
20 | 20 | |||||||
21 | 21 | |||||||
22 | 22 | |||||||
23 | 23 | |||||||
24 | 24 | |||||||
25 | 25 | |||||||
26 | 26 | |||||||
27 | 27 | |||||||
28 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F1 | F1 | =textjoin2(", ",TRUE,A1:A27) |
Function jec(r As Range) As String
jec = Join(Filter(Evaluate("transpose(if(" & r.Address & "<>""""," & r.Address & "))"), False, 0), ", ")
End Function
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 1 | |||||
2 | abc def | 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 | ||||
3 | 3 | |||||
4 | 4 | |||||
5 | 5 | |||||
6 | 6 | |||||
7 | 7 | |||||
8 | 8 | |||||
9 | ||||||
10 | ||||||
11 | ghi jkl | |||||
12 | ||||||
13 | 13 | |||||
14 | 14 | |||||
15 | 15 | |||||
16 | 16 | |||||
17 | 17 | |||||
18 | 18 | |||||
19 | 19 | |||||
20 | 20 | |||||
21 | 21 | |||||
22 | 22 | |||||
23 | 23 | |||||
24 | 24 | |||||
25 | 25 | |||||
26 | 26 | |||||
27 | 27 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2 | D2 | =jec(A1:A27) |
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
A B C D 1 1 2 abc def 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 3 3 4 4 5 5 6 6 7 7 8 8 9 10 11 ghi jkl 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25 25 26 26 27 27 Sheet1[many
Cell Formulas Range Formula D2 D2 =jec(A1:A27)
Cell Formulas | ||
---|---|---|
Range | Formula |
many thanks for your responses and your soulutionHow 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
A B C D 1 1 2 abc def 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 3 3 4 4 5 5 6 6 7 7 8 8 9 10 11 ghi jkl 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25 25 26 26 27 27 Sheet1
Cell Formulas Range Formula D2 D2 =jec(A1:A27)
many thanks for your responses and your soulution@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
B C D E 1 1 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 2 abc def 3 3 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 4 4 5 5 6 6 7 7 8 8 9 10 11 ghi jkl 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25 25 26 26 27 27 Sheet2
Cell Formulas Range Formula E1 E1 =TEXTJOIN2(", ",TRUE,IF(B1:B27<>"",B1:B27,"")) E3 E3 =TEXTJOIN(", ",TRUE,IF(B1:B27<>"",B1:B27,""))