Combining Excel Test? 1

[fpgiv]

Hi,
Is there a way to merge several cells of Excel data into one cell? They are all text cells, and I want to concatenate the text into one cell. I have been trying to find this for several hours, and I can't find anything. I know I can do it by copying and pasteing, but I'm hoping there is a faster way.
Thanks.

 

[straybullet]

In D1 =CONCATENATE(A1&" "&B1&" "&C1) will put together the contents of A1, B1 & C1 with spaces between the values.
You can add more cell references as needed.

Let them hate - so long as they fear... Lucius Accius

 

[KenWright]

How many cells? Are they all in a column or a row, or across a range of cells

How do you want to merge them, ie

with the following abc def ghi jkl, do you want

1) abcdefghijkl
2) abc,def,ghi,jkl
3) abc, def, ghi, jkl
4) abc def ghi jkl

etc

help us so we can help you

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[fpgiv]

Hi, Thanks, I know I can use the concatenate function, but there are hundred of cells that are all spread about. They are all generally in the same column, and I want them combined like abc def ghi jkl with a space in between.
Thanks!

 

[KenWright]

OK, the following assumes that you want the result returned to the cell A1 - Just change if not. Put the code in a normal module, select the range you want to combine and run the code - Note though, haven't catered for blanks in there, so shout if you need it:-

Sub Concat()

Dim str As String
Dim x As Long

str = Range("A1").Value

With Selection
For x = 1 To .Rows.Count
If str = "" Then
str = str & .Rows(x).Value
Else
str = str & " " & .Rows(x).Value
End If
Next x
End With

Range("A1") = str

End Sub

For a UDF, use the following from Gord Dibben in the MS groups:-

UDF......

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & " "
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Copy/paste this code to a general module in your workbook then in a cell enter

=ConCatRange(A1:A100)

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[fpgiv]

Awesome! Thanks a bunch!

 

[KenWright]

You're welcome

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Jdogga]

Thanks man!!! I also needed this.
You saved me much time. God bless!
-Jdogga