SOLVED
Home

New Spill function making concatenate output difficult?

%3CLINGO-SUB%20id%3D%22lingo-sub-881652%22%20slang%3D%22en-US%22%3ENew%20Spill%20function%20making%20concatenate%20output%20difficult%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881652%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20no%20Excel%20whiz%2C%20but%20I%20need%20to%20be%20able%20to%20concatenate%20the%20contents%20of%20cells%20in%209%20columns%20into%20one%2C%20for%20each%20row%20in%20a%201000%20row%20spreadsheet%2C%20with%20comma-delimited%20output.%20I%20used%20to%20be%20able%20to%20do%20this%20quite%20easily%20up%20until%20the%20most%20recent%20Excel%20update.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20the%20concatenate%20function%20uses%20Excel's%20new%20%22SPILL%22%20function%20to%20paste%20the%20result%20into%20adjoining%20empty%20cells%20next%20to%20the%20one%20I%20am%20placing%20my%20formula%20in.%20Isn't%20the%20reason%20for%20Concatenate%20to%20join%20the%20selected%20cells%20text%20into%20one%20cell%3F%20Anyway%20-%20I%20have%20not%20found%20a%20way%20that%20I%20can%20bring%20the%20output%20back%20into%20just%20one%20cell.%20I%20need%20to%20be%20able%20to%20copy%20these%20outputs%20into%20another%20spreadsheet%2C%20and%20while%20highlighting%20a%20cell%20in%20the%20spill%20output%20shows%20the%20blue%20outline%20for%20the%20range%2C%20copying%20the%20first%20cell%20only%20copies%20the%20first%20cells%20contents.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-881652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econcatenate%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Espill%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881670%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Spill%20function%20making%20concatenate%20output%20difficult%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881670%22%20slang%3D%22en-US%22%3E%3CP%3ENever%20mind%20-%20I%20have%20realised%20that%20CONCAT%20appears%20to%20have%20superseded%20CONCATENATE%2C%20and%20using%20CONCAT%20is%20giving%20me%20the%20output%20I%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881981%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Spill%20function%20making%20concatenate%20output%20difficult%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417119%22%20target%3D%22_blank%22%3E%40nickcicc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20to%20use%20TEXTJOIN%20of%20you'd%20like%20to%20add%20delimiter%3C%2FP%3E%3C%2FLINGO-BODY%3E
nickcicc
New 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?

2 Replies
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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies