Concatenate range of cells

[philip1127]

Hi - I'm trying to concatenate a range of cells, (A1:A250). Is there any way to accomplish this without typing (A1&A2&A3...&A250)? Thanks so much.

-Philip

 

[Aladin Akyurek]

Yes. MCONCAT is the function to use.

Download the Morefunc add-in from:

http://longre.free.fr/english/index.html

 

[philip1127]

Hi - thanks for the quick reply. I tried using the function, but I think I'm running into a problem that one cell can only hold so much data? Is that true?

The end result of this is trying to create one long text string that I can turn into a sequential file.

Thanks.

 

[Al Chara]

Try the following code:
<pre>Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A1:A250")
For Each cel In rng
x = x & cel.Value
Next
.Range("B1").Value = x
End With
End Sub</pre>

 

[Al Chara]

Excel's limitations are as follows (from some tech support page):

Microsoft Excel 7.0 (95)
-----------------------------------------
· Maximum Sheet Size: 16,384 rows by 256 columns
· Column Width: 0 to 255 characters
· Maximum length of cell contents (text): 255 chacters

Microsoft Excel 8.0 (97)
--------------------------------------------
· Maximum Sheet Size: 65,536 rows by 256 columns
· Column Width: 0 to 255 characters
· Maximum length of cell contents (text): 32,000 characters


Microsoft Excel 2000
--------------------------------------------
· Worksheet size: 65,536 rows by 256 columns
· Column Width: 255 characters
· Length of cell contents (text): 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

 

[tcnt9176]

I used it and it works great. I altered somewhat because I needed include a separator ", ". It works but how can I change it to take off the ", " after the last cell in Range.

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)
For Each cel In rng
x = x & cel.Value & ", "
Next
.Range("K1").Value = x
End With
End Sub

 

[tcnt9176]

ok. I researched & finally figured it out for those that may need this in the future. This code will concatenate a Range (column d in this example) with ", " & then take out the ", " on the last one. The result is provided in "K1".

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
Dim myString As String
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)
For Each cel In rng
x = x & cel.Value & ", "
Next
.Range("K1").Value = Left(x, Len(x) - 2)
End With
End Sub

 

[Rick Rothstein]

You might find the code at this mini-blog article of mine useful (it sounds like it will do exactly what you are asking for)...

Flexible Concatenation Function

 

[MJZimmer88]

ok. I researched & finally figured it out for those that may need this in the future. This code will concatenate a Range (column d in this example) with ", " & then take out the ", " on the last one. The result is provided in "K1".

This may be a bit late... but with a slight revision I have this working simply to remove the extra comma at the end:


Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
Dim y As String


With ActiveSheet


Set rng = .Range("A2:A" & Range("A65536").End(xlUp).Row)


For Each cel In rng


x = x & cel.Value & ", "
y = Len(x)
Next


.Range("B1").Value = Left(x, y - 2)


End With
End Sub

 

[gkudligi]

I have another suggestion which is a bit simpler

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range

With ActiveSheet

Set rng = .Range("A2:A" & Range("A65536").End(xlUp).Row)

For Each cel In rng

x = x & ", " & cel.Value

Next

.Range("B1").Value = x


End With
End Sub

This may be a bit late... but with a slight revision I have this working simply to remove the extra comma at the end:


Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
Dim y As String


With ActiveSheet


Set rng = .Range("A2:A" & Range("A65536").End(xlUp).Row)


For Each cel In rng


x = x & cel.Value & ", "
y = Len(x)
Next


.Range("B1").Value = Left(x, y - 2)


End With
End Sub