Join multiple cells into single cell with VBA

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

I want to join multiple cells C:P columns, into single column R for that using formula getting the result as below</SPAN></SPAN>

Getting result using formula...</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRST
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P14Join
622 | | | | | | | | | | | | |
7222 | 2 | | | | | | | | | | | |
82222 | 2 | 2 | | | | | | | | | | |
922222 | 2 | 2 | 2 | | | | | | | | | |
102222222 | 2 | 2 | 2 | 2 | 2 | | | | | | | |
1122222222 | 2 | 2 | 2 | 2 | 2 | 2 | | | | | | |
122222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | | | |
13222222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | |
1422222222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
15222222222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
16XX | | | | | | | | | | | | |
17X2X | 2 | | | | | | | | | | | |
182X2 | X | | | | | | | | | | | |
1922X2 | 2 | X | | | | | | | | | | |
20X222X | 2 | 2 | 2 | | | | | | | | | |
21222X2 | 2 | 2 | X | | | | | | | | | |
22X2222X | 2 | 2 | 2 | 2 | | | | | | | | |
23X22222X | 2 | 2 | 2 | 2 | 2 | | | | | | | |
2422222X2 | 2 | 2 | 2 | 2 | X | | | | | | | |
25X222222X | 2 | 2 | 2 | 2 | 2 | 2 | | | | | | |
26222222X2 | 2 | 2 | 2 | 2 | 2 | X | | | | | | |
27X2222222X | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | | | | |
282222222X2 | 2 | 2 | 2 | 2 | 2 | 2 | X | | | | | |
29X22222222X | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | | | |
302X22222222 | X | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | | | |
31
32
Sheet15
Cell Formulas
RangeFormula
R6=C6&" | "&D6&" | "&E6&" | "&F6&" | "&G6&" | "&H6&" | "&I6&" | "&J6&" | "&K6&" | "&L6&" | "&M6&" | "&N6&" | "&O6&" | "&P6


Required result like show as below... </SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRST
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P14Join
622
7222 | 2
82222 | 2 | 2
922222 | 2 | 2 | 2
102222222 | 2 | 2 | 2 | 2 | 2
1122222222 | 2 | 2 | 2 | 2 | 2 | 2
122222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
13222222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
1422222222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
15222222222222222 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
16XX
17X2X | 2
182X2 | X
1922X2 | 2 | X
20X222X | 2 | 2 | 2
21222X2 | 2 | 2 | X
22X2222X | 2 | 2 | 2 | 2
23X22222X | 2 | 2 | 2 | 2 | 2
2422222X2 | 2 | 2 | 2 | 2 | X
25X222222X | 2 | 2 | 2 | 2 | 2 | 2
26222222X2 | 2 | 2 | 2 | 2 | 2 | X
27X2222222X | 2 | 2 | 2 | 2 | 2 | 2 | 2
282222222X2 | 2 | 2 | 2 | 2 | 2 | 2 | X
29X22222222X | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
302X22222222 | X | 2 | 2 | 2 | 2 | 2 | 2 | 2
31
32
Sheet16


Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Formula: =Concatenate_Range(C6:P6,"|")

Code: 
Function Concatenate_Range(myrange As Range, Optional mydelimiter As String)
Dim Cell As Range

Application.Volatile
For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & Cell & mydelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next Cell

If Len(mydelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(mydelimiter))
End Function
 
Last edited:
Upvote 0
njimack said:
Formula: =Concatenate_Range(C6:P6,"|")

Code: 
Function Concatenate_Range(myrange As Range, Optional mydelimiter As String)
Dim Cell As Range

Application.Volatile
For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & Cell & mydelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next Cell

If Len(mydelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(mydelimiter))
End Function
Click to expand...
njimack, thank you function worked fine, but I need a VBA if possible and space after and before vertical bar</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
jkpieterse said:
If you have Excel from Office 365 you should be able to use the new TEXTJOIN function.
Click to expand...
jkpieterse, thank you for the suggestion, but I am using Excel 2000
 
Last edited:
Upvote 0
Kishan said:
njimack, thank you function worked fine, but I need a VBA if possible and space after and before vertical bar</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
Click to expand...
njimack, just realized space is not a problem just putting a formula like this =Concatenate_Range(C6:P6," | ") space is solved </SPAN></SPAN>

but will like to have a VBA if possible </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
You can call the function from a Sub...

Code: 
Sub Join_Cells()

Dim LastRow As Long
Dim c As Range
LastRow = Cells(5, 3).End(xlDown).Row
For Each c In Range(Cells(6, 18), Cells(LastRow, 18))
    c.FormulaR1C1 = "=Concatenate_Range(RC[-15]:RC[-2],""| "")"
    c.Formula = c.Value
Next c

End Sub
 
Upvote 0
Another way :
Code: 
Sub Join_Cells()
Dim cel As Range, ray As Variant, r As Range, i&
Dim rng As Range: Set rng = Range([C2], Cells(Rows.Count, "C").End(xlUp))
On Error Resume Next
For Each cel In rng
    Set r = cel.Resize(, 13).SpecialCells(xlCellTypeConstants)
    ReDim ray(1 To r.Count)
    For i = 1 To r.Count
        ray(i) = r(i).Value
    Next
    Cells(cel.Row, "R") = Join(ray, " | ")
Next
End Sub
 
Upvote 0
njimack said:
You can call the function from a Sub...

Code: 
Sub Join_Cells()

Dim LastRow As Long
Dim c As Range
LastRow = Cells(5, 3).End(xlDown).Row
For Each c In Range(Cells(6, 18), Cells(LastRow, 18))
    c.FormulaR1C1 = "=Concatenate_Range(RC[-15]:RC[-2],""| "")"
    c.Formula = c.Value
Next c

End Sub
Click to expand...
njimack, Thank you but need function to get results. </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
footoo said:
Another way :
Code: 
Sub Join_Cells()
Dim cel As Range, ray As Variant, r As Range, i&
Dim rng As Range: Set rng = Range([C2], Cells(Rows.Count, "C").End(xlUp))
On Error Resume Next
For Each cel In rng
    Set r = cel.Resize(, 13).SpecialCells(xlCellTypeConstants)
    ReDim ray(1 To r.Count)
    For i = 1 To r.Count
        ray(i) = r(i).Value
    Next
    Cells(cel.Row, "R") = Join(ray, " | ")
Next
End Sub
Click to expand...
footoo, I liked the VBA solution it is working as required :beerchug:</SPAN></SPAN>

Thank you so much for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :)</SPAN></SPAN>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Adding a NR to a formula & changing an IF to an IFS statement.
Replies
3
Views
291
Marvo
M
B
  • Solved
IF dates
Replies
9
Views
310
BrutalLogiC
B
K
  • Question Question
Need formula help (multiple results check?)
Replies
7
Views
556
Kishan
K
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P
Z
  • Question Question
Trying to use a Macro to fix timesheet numbers for regular time and overtime.
2
Replies
13
Views
465
myall_blues
myall_blues

Forum statistics

Threads
1,223,627
Messages
6,173,417
Members
452,514
Latest member
cjkelly15

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