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.