Concatenate question

[mikewg]

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?

 

[Richard Schollar]

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

 

[mikewg]

Exactly what I needed, thanks.

 

[mikewg]

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?

 

[Richard Schollar]

Mike

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

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

 

[behnm]

Downloading morefunc add-in

How do I download the morefunc add-in so I can use the MCONCAT function?

 

[Richard Schollar]

Hi Mark

Go here:

http://xcell05.free.fr/english/index.html

Best regards

Richard

 

[behnm]

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?

 

[Aladin Akyurek]

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,",")...

 

[SidBord]

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.