Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenation | Excel Discussion (Misc queries) | |||
Concatenation | Excel Discussion (Misc queries) | |||
Concatenation | Excel Discussion (Misc queries) | |||
concatenation | Excel Worksheet Functions | |||
concatenation | Excel Discussion (Misc queries) |