How-to Add a Hard Return to an Excel Concatenate Formula

Like most Excel users, one of the first formulas that you ever learned to master was the Concatenate Function.

But how do you add other controls like a Hard Return or a Tab to your Concatenate formula?

Recently, I had a similar request from a fan.  The fan asked, how they can add a Hard Return.

The quick answer is to use the CHAR() function.  The number for a hard return is coded as 10.  Check out the short video below to see how we found out the Hard Return code.

So that answer is to add CHAR(10) in your concatenate function where you want the hard return.

Here is a sample concatenate formula:

=Concatenate(“Line 1″,Char(10),”Line 2”)

This will give you a hard return between the text line 1 and line 2.

However, to view the hard return in Excel, you will have to change the cell formatting to “Wrap Text”

 

Also, you should consider forgetting the Concatenate function and use the Ampersand “&”.  Check out this tutorial that explains this topic:

How NOT to use Concatenate Function to build Dynamic Text in your Excel Dashboard Templates

Let me know if you think this is easier than using the Concatenate function in the comments below.

 

Video Demonstration


Finally, if 10 that is the code for a hard return, do you know what the code is for a tab?

Steve=True