Concatenation help
 

I need to concatenate close to 200 text cells into one. How can I achive
this without need for typing each and every cell reference in the formula.

I tried the below statement but it returns me an error

=CONCATENATE('Survey Details'!J8:HZ8)

Any help will be appreciated.

Concatenation help
 

You could use this UDF. Open VBE (Alt+F11) and insert a general module.
(Insert - Module). Paste the following in:

'===================
Function MyConcat(Text_Range As Range) As String
'Loops through each cell in your range
For Each cell In Text_Range
MyConcat = MyConcat & cell.Value
'alternative line, if you want a space inserted
'MyConcat = MyConcat & " " & cell.Value
Next cell

End Function
'===============

Close the VBE. Back in your workbook, the formula becomes:
=MyConcat('Survey Details'!J8:HZ8)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Naresh" wrote:

I need to concatenate close to 200 text cells into one. How can I achive
this without need for typing each and every cell reference in the formula.

I tried the below statement but it returns me an error

=CONCATENATE('Survey Details'!J8:HZ8)

Any help will be appreciated.

Concatenation help
 

Try this small User Defined Function:

Public Function concat(rr As Range) As String
concat = ""
For Each r In rr
concat = concat & r.Value
Next
End Function

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function =concat(A1:Z1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

--
Gary''s Student - gsnu200905


"Naresh" wrote:

I need to concatenate close to 200 text cells into one. How can I achive
this without need for typing each and every cell reference in the formula.

I tried the below statement but it returns me an error

=CONCATENATE('Survey Details'!J8:HZ8)

Any help will be appreciated.

Concatenation help
 

Thanks Luke for the help. It worked.

"Luke M" wrote:

You could use this UDF. Open VBE (Alt+F11) and insert a general module.
(Insert - Module). Paste the following in:

'===================
Function MyConcat(Text_Range As Range) As String
'Loops through each cell in your range
For Each cell In Text_Range
MyConcat = MyConcat & cell.Value
'alternative line, if you want a space inserted
'MyConcat = MyConcat & " " & cell.Value
Next cell

End Function
'===============

Close the VBE. Back in your workbook, the formula becomes:
=MyConcat('Survey Details'!J8:HZ8)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Naresh" wrote:

I need to concatenate close to 200 text cells into one. How can I achive
this without need for typing each and every cell reference in the formula.

I tried the below statement but it returns me an error

=CONCATENATE('Survey Details'!J8:HZ8)

Any help will be appreciated.

Concatenation help
 

Hi,

Download and install the following addin
http://www.download.com/Morefunc/300...-10423159.html and then use the
mconcat() function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Naresh" wrote in message
...
I need to concatenate close to 200 text cells into one. How can I achive
this without need for typing each and every cell reference in the formula.

I tried the below statement but it returns me an error

=CONCATENATE('Survey Details'!J8:HZ8)

Any help will be appreciated.