How to CONCATENATE multiple cell in single cell with separator

[ThrottleWorks]

Hi,

I have a list. I am trying to concatenate this list in a single cell using separator.

For example, I have Cell A1,A2,A3 and A4. Values are a1,a2,a3 and a4.

I want result in cell B1 as a1-a2-a3-a4.

Can anyone please help me this.

 

[Deepak]

Check this..

http://forum.chandoo.org/threads/concatenate.23045

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

 

[jayalaxmi]

use this

=G2&"-"&G3&"-"&G4&"-"&G5&"-"&G6&"-"&G7&"-"&G8

 

[ThrottleWorks]

Hi @Deepak , thanks a lot. I am checking this. Have a nice day ahead.

 

[Khalid NGO]

Hi Sachin,
This one too:
=CONCATENATE(TRANSPOSE(A2:A6)&"-")
Do not press enter...

select the part:
TRANSPOSE(A2:A6)&"-"
and press F9

you will see:
=CONCATENATE({"Marc-","Robin-","Sachin-","Brian-","Rahul-"})
now remove 2 curly brackets {} and press enter.

See the link for more info:
http://chandoo.org/wp/2014/01/13/combine-text-values-quick-tip/

Regards,

 

[ThrottleWorks]

@Khalid , thanks for the help. I think I have tried this.
But want do it with VBA and list is dynamic.

Have a nice day ahead.

 

[ThrottleWorks]

I am trying below code.

Dim Rng1 As Range
Set Rng1 = Range("n1:n" & EndRowNum)
            For Each Rng In Rng1
                Range("p1") = "=CONCATENATE(N1&"";""&N2)"
            Next Rng

But not sure how to replace N1 with changing reference of row number.

 

[ThrottleWorks]

I think, I got it. Still testing. Below is the code.

Sub merge()
Dim arg As String, n As Integer
For n = 2 To 4
arg = arg & ";" & Cells(n, 10)
Next
Range("p1") = arg
End Sub

Copied from http://www.ozgrid.com/forum/showthread.php?t=40469

Thanks everyone for their help.

 

[Deepak]

I think, I got it. Still testing. Below is the code.

Sub merge()
Dim arg As String, n As Integer
For n = 2 To 4
arg = arg & ";" & Cells(n, 10)
Next
Range("p1") = arg
End Sub

Copied from http://www.ozgrid.com/forum/showthread.php?t=40469

Thanks everyone for their help.

Change this
Range("p1") = Mid(arg, 2)

 

[ThrottleWorks]

@Deepak , Sure, please give me a moment.

 

[Deepak]

I am trying below code.

Dim Rng1 As Range
Set Rng1 = Range("n1:n" & EndRowNum)
            For Each Rng In Rng1
                Range("p1") = "=CONCATENATE(N1&"";""&N2)"
            Next Rng

But not sure how to replace N1 with changing reference of row number.

This would be

Dim Rng1 As Range
Set Rng1 = Range("n1:n" & EndRowNum)

For Each Rng In Rng1
    arg = arg & ";" & Rng
Next Rng

Range("p1") = Mid(arg, 2)

 

[ThrottleWorks]

@Deepak , somehow it's not working at my PC. Will re-check if I have committed any errors.

Thanks for the help.

 

[Deepak]

@Deepak , somehow it's not working at my PC. Will re-check if I have committed any errors.

Thanks for the help.

Just checked & find working

Sub test()

Dim Rng1 As Range, Rng As Range, EndRowNum As Integer
EndRowNum = 10

Set Rng1 = Range("n1:n" & EndRowNum)

For Each Rng In Rng1
    arg = arg & ";" & Rng
Next Rng

Range("p1") = Mid(arg, 2)
End Sub

 

[Marc L]

Hi !

Without a loop with Transpose & Join ? …
​

 

[Deepak]

Hi !

Without a loop with Transpose & Join ? …
​

Hi @Marc L

Same can be achieved something like as

Set Rng1 = Range("b2:b" & EndRowNum)
Range("c20") = Join(Application.Transpose(Rng1), "-")

but if ranges having spaces or same ranges needs to omit then this would not be proper.

 

[Marc L]

Like my thought in one code line …
​

 

[ThrottleWorks]

@Deepak , Good Afternoon, that's just awesome ! Have a nice day ahead.

 

[Deepak]

@Deepak , Good Afternoon, that's just awesome ! Have a nice day ahead.

Same 2 u dear!!