Insert Line Breaks with CONCATENATE
Among Excel’s wide range of functions is the CONCATENATE function. It is a text-combining function and is represented by &.
Usually, it is written in one of these two ways:
- =CONCAT(text1, text2, [text3],..)
- =text1 & text2 & text3 &… textN
Let’s use the previous sentence as an example here. Say you want the following two phrases to be combined into one cell.
(1) Peter Piper picked a peck of pickled peppers.
(2) A peck of pickled peppers Peter Piper picked.
Enter appropriate arguments in the formula. Since our text is in cells A2 and A3, we will use the following formula:
=A2 & ” ” & A3
Upon exiting the edit mode, Microsoft Excel will show the sentence in a single line.
But that’s not what we wanted. Why didn’t the line break appear? 🤔
That’s because the Enter keyboard shortcut does not work with formulas. Excel needs the CHAR function for inserting line breaks with functions.
So, to insert a line break:
- We will enable wrap text feature from the Home Tab for the selected cell. It is under the alignment group.
Once the toggle button is switched on for the selected cell, we will add the CHAR function. Each OS has a special character code, and the code for a line break in Windows is 10 and 13 for Mac.
- Add CHAR(10) in the formula in place of ” ” to add a line break
=A2 & CHAR(10) & A3.
The line feed appears in the Excel cell.
You can also use the TEXTJOIN function in place of CONCATENATE – they both serve the same purpose.