Concatenate many cells

[art]

Hello:

Is there any easy way to concatenate many cells to one cell. I have row from
A11 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

Is there a way to do it with an array or range formula?

Thanks for any help.

Art.

 

[Jacob Skaria]

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter <> vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

If this post helps click Yes

 

[Jacob Skaria]

Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..

A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")


Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter <> vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function

If this post helps click Yes

 

[Ashish Mathur]

Hi,

You can download and install the xlmorefunc5 addin and then use the
mconcat() function.

http://www.download.com/Morefunc/3000-2077_4-10423159.html


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

You can download and install the xlmorefunc5 addin and then use the
mconcat() function.

http://www.download.com/Morefunc/3000-2077_4-10423159.html


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Dave Peterson]

One more...

JE McGimpsey created a UDF called MultiCat:
http://mcgimpsey.com/excel/udfs/multicat.html

 

[Dave Peterson]

One more...

JE McGimpsey created a UDF called MultiCat:
http://mcgimpsey.com/excel/udfs/multicat.html

 

[Rick Rothstein]

Here is a UDF solution that doesn't rely on a loop (although it is limited
to a single row range or a single column range, but not a range having more
than one row *and* column). As Jacob indicated, you install the UDF by
launching the VB editor using the keystroke combination Alt+F11, then click
Insert/Module from the VBE menu and then Copy/Paste the below code into the
code window that opened up...

Function JoinString(varRange As Range, _
Optional varDelimiter As String) As String
With WorksheetFunction
If varRange.Columns.Count = 1 Then
JoinString = .Trim(Join(.Transpose( _
varRange), varDelimiter))
Else
JoinString = .Trim(Join(.Transpose(.Transpose( _
varRange)), varDelimiter))
End If
JoinString = Replace(Replace(.Trim(Replace(Replace( _
JoinString, " ", Chr(1)), varDelimiter, " ")), _
" ", varDelimiter), Chr(1), " ")
End With
End Function

Now you can use the JoinString function just like a built-in worksheet
formula; just pass the range and optional delimiter into it as arguments.
For you question, you would use this formula...

=JoinString(A11," ")

 

[Rick Rothstein]

Here is a UDF solution that doesn't rely on a loop (although it is limited
to a single row range or a single column range, but not a range having more
than one row *and* column). As Jacob indicated, you install the UDF by
launching the VB editor using the keystroke combination Alt+F11, then click
Insert/Module from the VBE menu and then Copy/Paste the below code into the
code window that opened up...

Function JoinString(varRange As Range, _
Optional varDelimiter As String) As String
With WorksheetFunction
If varRange.Columns.Count = 1 Then
JoinString = .Trim(Join(.Transpose( _
varRange), varDelimiter))
Else
JoinString = .Trim(Join(.Transpose(.Transpose( _
varRange)), varDelimiter))
End If
JoinString = Replace(Replace(.Trim(Replace(Replace( _
JoinString, " ", Chr(1)), varDelimiter, " ")), _
" ", varDelimiter), Chr(1), " ")
End With
End Function

Now you can use the JoinString function just like a built-in worksheet
formula; just pass the range and optional delimiter into it as arguments.
For you question, you would use this formula...

=JoinString(A11," ")

 

[Harlan Grove]

You can download and install the xlmorefunc5 addin and then use the
mconcat() function.

....

You could, but as an XLL, MOREFUNC can only return strings of 255 or
fewer characters. In this particular case, VBA udfs are far more
powerful/flexible than XLL add-ins.

 

[Harlan Grove]

You can download and install the xlmorefunc5 addin and then use the
mconcat() function.

....

You could, but as an XLL, MOREFUNC can only return strings of 255 or
fewer characters. In this particular case, VBA udfs are far more
powerful/flexible than XLL add-ins.

 

[Harlan Grove]

Is there any easy way to concatenate many cells to one cell. I have row from
A11 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

Is there a way to do it with an array or range formula?

If you may want to handle arrays as well as ranges, here's a link to
yet another alternative.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/33d73b08b1369a56

The mcat function shown in that link doesn't provide for an optional
delimiter srting. You'd need to append one to each argument. For
example,

=MID(mcat("|"&{"a","b","c"},
"|"&{"d","e","f","g";"h","i","j","k";"l","m","n","o"},
"|"&"p"),2,32767)

returns "a|b|c|d|h|l|e|i|m|f|j|n|g|k|o|p". Note that iteration through
2D arrays or ranges is by row then by column. You'd need to transpose
2D arrays or ranges you want to concatenate by column then by row.

 

[Harlan Grove]

Is there any easy way to concatenate many cells to one cell. I have row from
A11 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)

Is there a way to do it with an array or range formula?

If you may want to handle arrays as well as ranges, here's a link to
yet another alternative.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/33d73b08b1369a56

The mcat function shown in that link doesn't provide for an optional
delimiter srting. You'd need to append one to each argument. For
example,

=MID(mcat("|"&{"a","b","c"},
"|"&{"d","e","f","g";"h","i","j","k";"l","m","n","o"},
"|"&"p"),2,32767)

returns "a|b|c|d|h|l|e|i|m|f|j|n|g|k|o|p". Note that iteration through
2D arrays or ranges is by row then by column. You'd need to transpose
2D arrays or ranges you want to concatenate by column then by row.

 

[art]

Thank you. However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,

A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0

JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99

And that's it, no zero values.

Thank you for your prompt response.

Art.

 

[Gord Dibben]

Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(a1:a10,"|") desired delimiter between quotes
'ignores blanks and zero cells
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text <> "" And Cell.text <> 0 Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange = Left(sbuf, Len(sbuf) - Len(Delim))

End Function


Gord Dibben MS Excel MVP