Concatenate range of data into one cell

[nagovind]

Dear All,

There are strings/ numbers (irrespective of data type) in cells say

A1 = Apple
B1 = 2435
C1 = Google
E1 = MSoffice
..
..
..
K1 = $240@04

The requirement is to concatenate all those data in to a cell say B1 separated by comma

Cell B1 shall contain Apple,2435,Google,MSoffice,.......,$240@04

Please advise

Thank you
Govind

 

[kchiba]

You could do the following, however, you will have a problem if the data includes a date

= A1&","&B1&","&C1&","&E1......

 

[Marc L]

Hi,

'cause it's the VBA Macros section a code example :​

    [B1].Value = Join(Application.Index([A1:K2].Value, 1), ",")

 

[nagovind]

@kchiba
Actually i need versatile program so that range has to be modified as required
So i need a VBA code

@ Marc L
Sub chk()
[B1].Value = Join(Application.Index([A1:A10].Value, 1), ":")
End Sub

I have used your code as above with the data in the cells A1 to A10
But there is no results in B1 after executing the code
Please advise

 

[kchiba]

Try the following,

it assumes that your data in is Row 1 starting Column A
and your result is in A3

Sub ConCat_Range()
Dim rng As Range
Dim lr As Integer
Range("a3").Clear
Set rng = Range("A1").CurrentRegion
lr = rng(rng.Count).Column
For Each r In rng
  Range("A3").Value = Range("A3").Value & "," & r
Next r
End Sub

 

[Marc L]

@ Marc L
I have used your code as above with the data in the cells A1 to A10
But there is no results in B1 after executing the code
Please advise

Your original post was for A1:K1 ‼

So you can adapt, just read the INDEX's help since the worksheet' screen …
​

 

[kchiba]

Hi Marc,

I am sure I am missing something but your code only picks up A1, would really like to see it work, it looks cool.

cheers

kanti

 

[nagovind]

Try the following,

it assumes that your data in is Row 1 starting Column A
and your result is in A3

Sub ConCat_Range()
Dim rng As Range
Dim lr As Integer
Range("a3").Clear
Set rng = Range("A1").CurrentRegion
lr = rng(rng.Count).Column
For Each r In rng
  Range("A3").Value = Range("A3").Value & "," & r
Next r
End Sub

Thank you for your reply

Your code works
1. But it adds a 'comma' even for first cell data
2. Please advise how to modify the code such that the data is in the subsequent ROWS instead of columns

Sincerely
Govind

 

[nagovind]

Thank you for your reply

Your code works
1. But it adds a 'comma' even for first cell data
2. Please advise how to modify the code such that the data is in the subsequent ROWS instead of columns

Sincerely
Govind

Wow

IT is working perfectly irrespective of the data is in row or columns

But please advise how to make it ONLY row data to be concatenated or Only column data to be concatenated

 

[kchiba]

Not sure what you mean by only ROW or only Column, if your data is in a column the column will be concatenated, if the data is in a row the row will be concatenated.

The thing is the Row or Column must be stand alone and not have other data adjacent to it. So please upload a file and spell out exactly what you need.

 

[Marc L]

My code example is for a row data and can be easily adapted for a column
just by reading the INDEX worksheet function's help ! …​

 

[nagovind]

Not sure what you mean by only ROW or only Column, if your data is in a column the column will be concatenated, if the data is in a row the row will be concatenated.

The thing is the Row or Column must be stand alone and not have other data adjacent to it. So please upload a file and spell out exactly what you need.

Thank you for your reply

"The thing is the Row or Column must be stand alone and not have other data adjacent to it"

Agreed

Closed. Thanks for the code

 

[Hui]

Using the Google Custom Search box at the Top Right of this and every screen at Chandoo.org would have revealed
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
It has many useful examples of codes in the comments