Improve the Concatenate Function
Make Concatenate to Work with Range of Cells / Arrays rather than 1 cell separated by Comas
=CONCATENATE(Array,Seperator)
The Array to Accept A1:A10 or A1:D1 or {"Abb","Areva","BHEL"} or {"ABB";"Areva";"BHEL}
or

Update – 2016-03-07
Hi All,
Just wanted to let you know that we’ve completed adding new =CONCAT and =TEXTJOIN functions to Excel 2016 (as part of Office 365 – builds 16.0.6528 and later) that should do what’s been requested here. You can read more about it on our blog:
Cheers,
Dan [MS]
19 comments
-
Sameer Bhide commented
Only in the Subscription product and not in the MSI version - This is a huge disappointment - we cant distribute solutions based on TEXTJOIN - when will this be available in the MSI versions
-
Sameer Bhide commented
It also has TEXTJOIN which accepts Delimiters - This is simply great
-
Sameer Bhide commented
@ Excel Team - Great work
After yesterday's update - Excel 2016 now has CONCAT !!! which accepts range of cells as a parameter
- Now ever there is now parameter to accept a delimiter - we have to resort to array entered formulas
=CONCAT(A1:A10," ") - Array entered -
ms@baccma.co.uk commented
I have an array {"a","b","c","d"} and want to return a single string "abcd". CONCATENATE() won't do it at present and nor will anything else. Could you get CONCATENATE to work with arrays?
-
Cori commented
A new Function that allows you to take the values in different cells and concatenate them into one cell deliminated by commas. For Example:
If I have A1 B1 and C1 with the values Red Blue and Green. I type =LIST(A1:C1) and the output is Red,Blue,Green -
Gareth Hayter commented
-
Brian Canes commented
Allow for concatenating an array or range
Regards
Brian -
aikimark1955 commented
You are welcome to use my BetterConcateFunction or use it as a model for your improvement.
http://rdsrc.us/GOGGgl -
S Saqib Husain commented
10 times Patrick's
-
Patrick Matthews commented
Yes, yes, one thousand times yes!
-
Jan Karel Pieterse commented
Thanks guys, great improvement!
-
Rick Rothstein (MVP - Excel) commented
Great news, but long overdue. If it helps any, you may find the way I implement a similar type function helpful as it regards the direction of concatenation for rectangular ranges. Here are links to the two articles I have posted on this in my mini-blog...
http://www.excelfox.com/forum/f22/flexible-concatenation-function-313/
http://www.excelfox.com/forum/f22/formatted-flexible-concatenation-function-582/
-
Felipe Costa Gualberto commented
Great news, Microsoft! This is going to boost formula text experience to a whole new level!
-
Gareth Hayter commented
Please also add an optional parameter to specify whether null/blank/whitespace-only elements should be omitted/skipped.
-
Thanks for your feedback! As Dan said, we're building your suggestion now.
-
Jan Wx, NZ commented
This can't come soon enough
-
Dan Battagin [MS XL] commented
Thanks for the feedback folks. Guess what?! We're working on this area right now.
-
Jon Peltier commented
Like VBA's Join() function.
-
Sanjib Pradhan commented
Nice Idea!