Forum Discussion
Susanna Hughes
May 25, 2017Copper Contributor
Help for CONCATENATE function
Hi - I am using the CONCATENATE funcation to combine several cells into one. I would like a hard return (Alt+Shift) after each cells data. Is this possible?
I knowt hat I can edit the formula and add &CHAR(10)& in lieu of commas between each, but need something that can be added to the initial formula.
Currently I have this:
Step 1
=CONCATENATE(TRANSPOSE(G1641:G1648))
Step 2
Select bold and F9
=CONCATENATE(TRANSPOSE(G1641:G1648))
Step 3
Delete {}
=CONCATENATE({"LEED Certified-Silver","Maintenance Facilities","Marina","New Construction","Office","Public-City","Site Work","Warehouse"})
Result
=CONCATENATE("LEED Certified-Silver","Maintenance Facilities","Marina","New Construction","Office","Public-City","Site Work","Warehouse")
Ultimately I would like to have this:
=CONCATENATE("Office"&CHAR(10)&"Renovation"&CHAR(10)&"Tenant Improvement")
But without having to go through Steps 1-3 and then adding &CHAR(10)& at each comma.
Any ideas? -Thanks!
- Detlef_LewinSilver Contributor
Hi
=TEXTJOIN(CHAR(10),TRUE,G1641:G1648)
- Susanna HughesCopper Contributor
Detlef_Lewin wrote:
Hi
=TEXTJOIN(CHAR(10),TRUE,G1641:G1648)
Thanks, but that formula returns #NAME?
- Detlef_LewinSilver Contributor
That means you are not an Office 365 subscriber. TEXTJOIN() is only available for subscribers or in Excel Online.