Join all row cells in a single cell by vertical bar

[motilulla]

Hello,</SPAN></SPAN>

I need to Join P:Y column each row cells in single cell by using a vertical bar, result require in AA column as shown below </SPAN></SPAN>

Example</SPAN></SPAN>

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10		Join
4	11	13										11 | 13
5	9	10	12	13								9 | 10 | 12
6	9	10	12									9 | 10 | 12
7	10	13										10 | 13
8	9	11	12									9 | 11 | 12
9	9	11	13									9 | 11 | 13
10	3	4	9	10								3 | 4 | 9 | 10
11	1	2	3	4	5	9						1 | 2 | 3 | 4 | 5 | 9
Sheet1

Thank you all </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[alansidman]

Using Power Query which is available in Excel Versions 2010 and later.

Mcode:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"n1", Int64.Type}, {"n2", Int64.Type}, {"n3", Int64.Type}, {"n4", Int64.Type}, {"n5", Int64.Type}, {"n6", Int64.Type}, {"n7", type any}, {"n8", type any}, {"n9", type any}, {"n10", type any}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Join", each Text.Combine({Text.From([n1], "en-US"), Text.From([n2], "en-US"), Text.From([n3], "en-US"), Text.From([n4], "en-US"), Text.From([n5], "en-US"), Text.From([n6], "en-US"), Text.From([n7], "en-US"), Text.From([n8], "en-US"), Text.From([n9], "en-US"), Text.From([n10], "en-US")}, "|"), type text)
in
    #"Inserted Merged Column"

Data Range

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	n1​	n2​	n3​	n4​	n5​	n6​	n7​	n8​	n9​	n10​	Join​
2​	11​	13​	​	​	​	​	​	​	​	​	11|13​
3​	9​	10​	12​	13​	​	​	​	​	​	​	9|10|12|13​
4​	9​	10​	12​	​	​	​	​	​	​	​	9|10|12​
5​	10​	13​	​	​	​	​	​	​	​	​	10|13​
6​	9​	11​	12​	​	​	​	​	​	​	​	9|11|12​
7​	9​	11​	13​	​	​	​	​	​	​	​	9|11|13​
8​	3​	4​	9​	10​	​	​	​	​	​	​	3|4|9|10​
9​	1​	2​	3​	4​	5​	9​	​	​	​	​	1|2|3|4|5|9​

 

[motilulla]

Using Power Query which is available in Excel Versions 2010 and later.

Mcode:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"n1", Int64.Type}, {"n2", Int64.Type}, {"n3", Int64.Type}, {"n4", Int64.Type}, {"n5", Int64.Type}, {"n6", Int64.Type}, {"n7", type any}, {"n8", type any}, {"n9", type any}, {"n10", type any}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Join", each Text.Combine({Text.From([n1], "en-US"), Text.From([n2], "en-US"), Text.From([n3], "en-US"), Text.From([n4], "en-US"), Text.From([n5], "en-US"), Text.From([n6], "en-US"), Text.From([n7], "en-US"), Text.From([n8], "en-US"), Text.From([n9], "en-US"), Text.From([n10], "en-US")}, "|"), type text)
in
    #"Inserted Merged Column"

Hello alansidman, may be it is good options but I have got excel 2000, thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Please need some solution which can work with my version Formula or VBA

 

[alansidman]

Assumes your data starts in A1. Adjust code ranges accordingly if your data is located differently

Option Explicit


Sub motil()
    Dim lc As Long, lr As Long, i As Long, j As Long
    Dim x As Variant
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr
        x = ""
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 1 To lc
            x = x & Cells(i, j) & "|"
        Next j
        Range("K" & i) = x
    Next i
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

 

[Rick Rothstein]

Here is a formula solution...

=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)," ","|")

 

[mohadin]

Hi

Sub test2()
    Dim b As Variant, x, lc
    Dim lr, i, j
   Range("k1:k10").ClearContents
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
       lc = Cells(i, Columns.Count).End(xlToLeft).Column
       b = Application.Transpose(WorksheetFunction.Transpose(Cells(i, 1).Resize(, lc)))
       b = Join(b, "|")
       Cells(i, 11) = b
    Next
End Sub

 

[mohadin]

Hi again
I suggest to the previous Thread code

Sub test3()
    Dim a, b As Variant, x
    Dim lr, i, t, j
    ReDim a(1 To 9)
    ReDim Preserve a(1 To 10)
    For i = 4 To Cells(Rows.Count, 5).End(xlUp).Row
        ReDim b(1 To 10)
        t = 1
        For j = 5 To 15
            If Cells(i, j) <> 0 Then
                b(t) = Cells(i, j): t = t + 1
            End If
        Next
        ReDim Preserve b(1 To t)
         Cells(i, 16).Resize(, UBound(b)) = b
        b = Join(b, "|")
        Cells(i, 26) = b
    Next
End Sub

 

[YasserKhalil]

Try this code

Sub Test()
    Dim c As Range
    
    For Each c In Range("P4:Y" & Cells(Rows.Count, "P").End(xlUp).Row).Rows
        Range("AA" & c.Row).Value = Replace(Trim(Join(Application.Index(c.Value, 0), " ")), " ", " | ")
    Next c
End Sub

 

[mohadin]

OOOOps
PLS. forget POST #7

Sub test2()
    Dim b As Variant, x
    Dim lr, lc, i
   Range("aa3:aa11").ClearContents
    For i = 3 To Cells(Rows.Count, 16).End(xlUp).Row
       lc = Cells(i, Columns.Count).End(xlToLeft).Column - 15
       b = Application.Transpose(WorksheetFunction.Transpose(Cells(i, 16).Resize(, lc)))
       b = Join(b, "|")
       Cells(i, 27) = b
    Next
End Sub