Forum Discussion

Susanna Hughes's avatar
Susanna Hughes
Copper Contributor
May 25, 2017

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_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        That means you are not an Office 365 subscriber. TEXTJOIN() is only available for subscribers or in Excel Online.

         

Resources