Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenation MichaelS Excel Discussion (Misc queries) 8 September 11th 08 07:54 AM
Concatenation Muthalaly Excel Discussion (Misc queries) 2 June 24th 08 12:57 PM
Concatenation orquidea Excel Discussion (Misc queries) 6 January 11th 08 08:19 PM
concatenation nandkishor Excel Worksheet Functions 2 September 5th 07 05:36 AM
concatenation mattguerilla Excel Discussion (Misc queries) 3 January 27th 06 12:47 AM


All times are GMT +1. The time now is 11:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"