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.