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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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 (y)</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

K
  • Question
Need formula help (multiple results check?)
Replies
7
Views
330
Kishan
K
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
912
pawcoyote
P
S
  • Question
Formula for Equity Vesting
Replies
6
Views
295
Dave Patton
D
Romano_odK
  • Solved
How to remove al line with VBA when the values is "OK"
Replies
9
Views
249
Peter_SSs
Peter_SSs
J
  • Solved
VBA for adding formulas to specific Columns when a new row is added.
Replies
7
Views
214
dreid1011
dreid1011

Forum statistics

Threads
1,214,593
Messages
6,120,435
Members
448,962
Latest member
Fenes

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