Yesterday i got this error when trying to put all the info from the hundreds cells that are populated into one cell.
Cause:
- My arguments exceed argument limit of concatenate formula. In 2007 concatenate supports only 255 arguments.
There are some resolution to get this done.
- Concatenate “Inception“.
For example you’ll concat all Cells from A1 to C100. So you should create 4 concatenations: - Concat A1 to A100, locate it on cell A101
- Concat B1 to B100, locate it on B101
- Concat C1 to C100, locate it on C101
- Combine A101, B101 and C101 into one cell.
- Instead of using the function you can use the operator &. I believe this is limited by the maximum length of the formula which varies with version. Just write =A1&A2&A3 … etc
- If your range is contiguous, you could create a custom UDF (User Defined Function)
Here’s my code to accomplish this purposeFunction ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & " " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function
And just write =concatrange(A1:C100) hope it’ll work like a charm.
Nice post. I learn something totally new and challenging on sites
I stumbleupon everyday. It’s always useful to read through content from other authors and
use a little something from their web sites.