Excel Concatenate Error: You’ve Entered Too Many Arguments

February 18, 2012 5:59 am
Andri Kasta Marengga

Excel Concatenate Error: You’ve Entered Too Many Arguments

Excel Concatenate Error. You've entered too many arguments

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.

  1. 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.
  2. 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
  3. If your range is contiguous, you could create a custom UDF (User Defined Function)
    Here’s my code to accomplish this purpose

    Function 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.

, ,

One Response to “Excel Concatenate Error: You’ve Entered Too Many Arguments”

  1. dieta refluksowa September 5, 2013 at 6:53 pm #

    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.

Leave a Reply