SOLVED

New Spill function making concatenate output difficult?

Copper Contributor

I'm no Excel whiz, but I need to be able to concatenate the contents of cells in 9 columns into one, for each row in a 1000 row spreadsheet, with comma-delimited output. I used to be able to do this quite easily up until the most recent Excel update.

 

Now, the concatenate function uses Excel's new "SPILL" function to paste the result into adjoining empty cells next to the one I am placing my formula in. Isn't the reason for Concatenate to join the selected cells text into one cell? Anyway - I have not found a way that I can bring the output back into just one cell. I need to be able to copy these outputs into another spreadsheet, and while highlighting a cell in the spill output shows the blue outline for the range, copying the first cell only copies the first cells contents.

 

Can anyone help?

6 Replies
best response confirmed by nickcicc (Copper Contributor)
Solution

Never mind - I have realised that CONCAT appears to have superseded CONCATENATE, and using CONCAT is giving me the output I need.

 

@nickcicc 

Better to use TEXTJOIN of you'd like to add delimiter

Thanks @nickcicc! This worked for me.

@nickcicc 

CONCATENATE was well-known as a piece of garbage that has little to offer over the "&" operator.

TEXTJOIN and CONCAT are the perfect partners for dynamic arrays.

@Peter Bartholomew 

Even less. I didn't google right now, from my memory CONCATENATE() as a function has some limit on number of parameters (you may use as many & as you wish) plus some limits on text length. On practice there is no difference but usability. Some prefer CONCATENATE as more understandable, not necessary to recognize every time what the hell & is. Other prefer & as the fastest way to write formula.

 

Not the same, but similar story with AND(a,b,c) and a*b*c.

@Sergei Baklan 

I agree, for three distinct text strings, it is largely a matter of taste.  It is the handling of arrays that led me to despise CONCATENATE so much.  One example was turning numbers into natural language.  I had strings representing units, thousands, millions and billions of dollars as elements of an array. Combining the sub-strings with

= CONCATENATE(INDEX(results,1), INDEX(results,2), INDEX(results,3), INDEX(results,4))

is rubbish; no better than

= INDEX(results,1) & INDEX(results,2) & INDEX(results,3) & INDEX(results,4)

I would go for

= CONCAT(results)

any day.  As you say, TEXTJOIN is often better still.

1 best response

Accepted Solutions
best response confirmed by nickcicc (Copper Contributor)
Solution

Never mind - I have realised that CONCAT appears to have superseded CONCATENATE, and using CONCAT is giving me the output I need.

 

View solution in original post