Concatenate question

mikewg

New Member
Joined
May 8, 2006
Messages
12
Is there an easier way to join 100 cells worth of text without putting:

=A2&A3&A4&A5&...&A100

Ideally, I'd love to be able to put in

=CONCATENATE(A2:A100)

but this doesn't seem to work. And writing out

=CONCATENATE(A2,A3,A4...)

is longer than just doing it the first way. Suggestions?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi Mike

If you can download add-ins, then download the morefunc add-in and use the MCONCAT function (see the recommeded downloads/add-ins message at the top of the forum).

Use like this:

=MCONCAT(A1:A100,",")

Best regards

Richard
 
Upvote 0
Each of cells contains a lot of text. When I use the formula for A2:A5, everything works great. But when I use it for A2:A6, I get #VALUE!. Same thing with different numbers: A5:A8 will work, but A5:A12 gives me the #VALUE! error also. Is it because I'm using too much text? I might have to just use A2&A3&A4&A5 manually because it doesn't freak out like this formula seems to be doing.

Thoughts?
 
Upvote 0
Mike

I'll be honest I'm not sure. Give the following UDF a go and see if it works any better:

Code:
Function concat(rng As Range, Optional delim As String = "") As String
Dim temp As String
For Each c In rng
    If Not Len(temp) = 0 Then
        temp = temp & delim & c.Value
    Else
        temp = c.Value
    End If
Next c
concat = temp
End Function

Once you've placed it in a standard module in the workbook that you want to use it in, use:

=concat(A1:A100,",")

the delimiter is optional, which means it will simply join the text together as is if you leave it out.

Richard
 
Upvote 0
Downloading morefunc add-in

How do I download the morefunc add-in so I can use the MCONCAT function?
 
Upvote 0
Thanks Richard. I got the add-in, but also have the same issue of having too much text. How do I place the UDF, you posted earlier in this topic, in a standard module in the workbook?
 
Upvote 0
Thanks Richard. I got the add-in, but also have the same issue of having too much text. How do I place the UDF, you posted earlier in this topic, in a standard module in the workbook?

MCONCAT concats up to 255 chars at a time...

Perhaps you can get away with something like:

=MCONCAT(A2:A10,",")&","&MCONCAT(A11:A25,",")...
 
Upvote 0
Are you possibly running into the Excel max length limit? The max number of characters in an Excel cell cannot exceed 32767 of which only 1024 will display. However all 32767 will display in the formula bar.
 
Upvote 0

Forum statistics

Threads
1,206,949
Messages
6,075,810
Members
446,158
Latest member
octagonalowl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top