Excel Concatenate Error: More arguments

November 12 2011 07:22 / by: Bryan Didas / Filed in:Microsoft Excel

Today while working on a spreadsheet, I needed to join a large amount of text and data into a string for use in an SQL script. Of course one method to join these segments or cells of data is to use the Concatenate function. I have used the function many times in the past and gotten pretty good at creating these strings. This particular project required that I join well over 100 arguments. I began entering and got into a rhythm after approx. 32 arguments I decided to stop and review. Selected OK on the input dialog box and bing…. error.

MoreArgs

After I verified that I didn’t have any argument errors I began deleting arguments when I got down to Text28 field I picked OK and the function accepted my arguments. So yes I suppose there is a limit, but I thought I could enter over 250?? So I entered one argument at time until I got the error again. There is a 30 argument limit (While searching for the answer I found that many other function have the same limitation).

I began looking into the error because I really need to join all of these arguments for my project and I figured this will be another opportunity to learn something.

What I find interesting is that the concatenate dialog box even states that 1 to 255 text strings can be joined. I suppose this is true, but you need to do a little something extra to get more than 30 arguments into the string.

Concatenate

 

Solution

The solution for stringing together more than 30 arguments in the Concatenate function is to structure your sting like so grouping each concatenate segment with no more than 30 arguments.

=concatenate(……..)&concatenate(…..)