Concatenation help

[Naresh]

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.

 

[Luke M]

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)

 

[NBVC]

Try adding this popular UDF to your VB editor Module


Code
-------------------
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Functio
-------------------


and then use formula

=aconcat('Survey Details'!J8:HZ8

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx

 

[Naresh]

Thanks Luke for the help. It worked.

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!*

 

[Ashish Mathur]

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com