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
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td]

n1​

[/td][td]

n2​

[/td][td]

n3​

[/td][td]

n4​

[/td][td]

n5​

[/td][td]

n6​

[/td][td]

n7​

[/td][td]

n8​

[/td][td]

n9​

[/td][td]

n10​

[/td][td]

Join​

[/td][/tr]


[tr][td]

2​

[/td][td]

11​

[/td][td]

13​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

11|13​

[/td][/tr]


[tr][td]

3​

[/td][td]

9​

[/td][td]

10​

[/td][td]

12​

[/td][td]

13​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

9|10|12|13​

[/td][/tr]


[tr][td]

4​

[/td][td]

9​

[/td][td]

10​

[/td][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

9|10|12​

[/td][/tr]


[tr][td]

5​

[/td][td]

10​

[/td][td]

13​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

10|13​

[/td][/tr]


[tr][td]

6​

[/td][td]

9​

[/td][td]

11​

[/td][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

9|11|12​

[/td][/tr]


[tr][td]

7​

[/td][td]

9​

[/td][td]

11​

[/td][td]

13​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

9|11|13​

[/td][/tr]


[tr][td]

8​

[/td][td]

3​

[/td][td]

4​

[/td][td]

9​

[/td][td]

10​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

3|4|9|10​

[/td][/tr]


[tr][td]

9​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

9​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

1|2|3|4|5|9​

[/td][/tr]
[/table]

 

[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