CONCATENATE

[Tservo]

I would LOVE to combine cells from the same column instead of cut & pasting
information. - example D132 through D147 and have this combined information
appear in D132. Is this possible?

 

[Joe User]

I would LOVE to combine cells from the same
column instead of cut & pasting information.
- example D132 through D147 and have this
combined information appear in D132. Is this
possible?

=D132 & D133 & ... & D146 & D147

You can separate the cell contents by concatenating strings. for example:

=D132 & " " & D133 & ... & D146 & " " & D147

Obvious, this is tedious for a large range like D132147. The CONCATENATE
function does not accept ranges either.

 

[Dianne]

I would LOVE to combine cells from the same column instead of cut
pasting

information. - example D132 through D147 and have this combine information
appear in D132. Is this possible?

=CONCATENATE(D132,D133,D134,D135,D136,D137,D138,D139,D140,D141,D142,D143,D144,D145,D146,D147
if you want spaces, add ," ", between each.

=CONCATENATE(D132," ",D133," ",D134," ",D135," ",D136," ",D137,
",D138," ",D139," ",D140," ",D141," ",D142," ",D143," ",D144," ",D145,
",D146," ",D147)

You will need to put it in another cell, copy the result and past
value into D132 to replace what is there and then delete all the value
in the other cells.

 

[Gord Dibben]

Simple form...........

In D131..............not D132!

=D132&D133&D134&D135 etc.

Not so simple form..............

=D132 $ " " D133 & " " & D134 etc.

Easy way.............use a UDF

Function ConCatRange22(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange22(a1:a10,", ") desired delimiter between quotes
'as written delimiter is comma space
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text <> "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange22 = Left(sbuf, Len(sbuf) - Len(Delim))

End Function

Paste the UDF to a General module in your workbook.


Gord Dibben MS Excel MVP

 

[Ashish Mathur]

Hi,

Download and install this addin -
http://www.download.com/Morefunc/3000-2077_4-10423159.html and then you can
use the MCONCAT() function

=mconcat(D132146," ")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com