Join all row cells in a single cell by vertical bar

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,355
Office Version
  1. 2010
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
PQRSTUVWXYZAA
1
2
3n1n2n3n4n5n6n7n8n9n10Join
4111311 | 13
591012139 | 10 | 12
6910129 | 10 | 12
7101310 | 13
8911129 | 11 | 12
9911139 | 11 | 13
10349103 | 4 | 9 | 10
111234591 | 2 | 3 | 4 | 5 | 9
Sheet1


Thank you all </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Using Power Query which is available in Excel Versions 2010 and later.

Mcode:
Code:
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​
 
Upvote 0
Using Power Query which is available in Excel Versions 2010 and later.

Mcode:
Code:
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>
 
Upvote 0
Please need some solution which can work with my version Formula or VBA
 
Last edited:
Upvote 0
Assumes your data starts in A1. Adjust code ranges accordingly if your data is located differently

Code:
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
 
Upvote 0
Here is a formula solution...

=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)," ","|")
 
Last edited:
Upvote 0
Hi
Code:
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
 
Upvote 0
Hi again
I suggest to the previous Thread code
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
 
Upvote 0
Try this code
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
 
Last edited:
Upvote 0
OOOOps
PLS. forget POST #7

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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