Add Line Break in Excel Formula

It’s easy to add a line break when you’re typing in an Excel worksheet. Just click where you want the line break, and press Alt + Enter. But how can you add line break in Excel formula?

Line Break in Excel Text

Here is an example of adding a line break in Excel text.

• The cursor is positioned where the line break should be added.
• Press Alt+Enter
• Press Enter, to complete the entry and move to another cell

When you press the Enter key to complete the entry, the line break appears, and Wrap Text is automatically added to the cell.

You might have to adjust the column width though, because the text won’t flow into the next column.

Add a Line Break in a Formula

It’s not quite as easy to add a line break in a formula, but it’s possible!
Here is a formula that shows text, combined with the sum of the values in C1:C6

=”Total amount is: ” & SUM(C1:C6)

To add a line break between the text and the total amount, use the Excel CHAR function, with the number 10. The “&” operator is included too,  to join the line break character to the other text in the formula.

=”Total amount is: ” & CHAR(10) & SUM(C1:C6)

Turn on Wrap Text

After you add the line break, and press Enter, you might see a strange little box, where the line break should be.

The Wrap Text feature isn’t automatically turned on, when you add a line break in a formula. You’ll have to turn it on yourself.

Select the cell with the line break in the formula, and on the Ribbon’s Home tab, click the Wrap Text command.

Then, you’ll see the line break in the cell, instead of the little square.

__________

0 thoughts on “Add Line Break in Excel Formula”

1. Martin says:

last week, in one of the LinkedIn Excel groups, someone posted a problem, which I offered to help, and part of my solution was to display concatenated cells that had at the end the CHAR(10), so the final text looked like one row per each cell included on the CONCATENATE.

I did exactly as you said, and… well, it didn’t showed up the way I wanted to… Maybe I am missing something?

1. John says:

It doesn’t work. Must be version issues

2. Joe says:

Custom formatting also has the advantage of maintaining the cell(s) as numeric values.

3. Rick Rothstein (MVP - Excel) says:

@Martin,

You have to turn Word Wrap on for the cells you want to display multiple lines. You do this by calling up the “Cell Format” dialog box for those cells, click the Alignment tab and put a check mark in the “Wrap text” check box and then click OK.

4. Martin says:

Thanks Rick, I’d done that, that was the origin of my question.

Rgds.

1. @Martin, you would need to include the CHAR(10) after each cell in the formula, not just once at the end.

5. This is a very useful trick of excel. Thanks for sharing. My excel cell texts used to be very long before applying this technique.

6. RF says:

It’s very helpful! Thank you for sharing!

7. AWR says:

worked perfectly. Thank you!

8. MJG says:

Thank you for posting!!

9. GES says:

I want to display a day and time on two lines within a cell. I have dd-mmm and hh:mm I want to automatically wrap so that the day and month display above the hour and minute. Using the &CHAR(10)& code only creates an error window.
8 Oct
10:33
Is the desired result.

10. GES says:

From another site the solution is to insert the line break command between the date and time, ^j (Do NOT type the carrot and “j”.) What needs to be done is hold the ctrl key town and hit the “j” key.
Under custom formatting, I typed in
d-mmm^jhh”:”mm
And it worked! :o)

It worked great for me with the CHAR(10) in a formula and ‘Wrap Text’ function. Thanks.

12. MANOJ says:

Madam, I see your formules to insert in line formuules but I have one problems is that I type another words after formules the entire line not work

13. JNO says:

I used the CHAR(10) and wrap text strategy, and it resolved my problem. Thanks a lot.

14. Girish says:

thanks a lot for CHAR(10) 🙂

15. Michael says:

CHAR(13) for Apple iOS, i.e. Mac. Everything else worked like a charm.

16. Faheem Ur Rehman says:

Well done, Debra Dalgleish; my sincere regards.

1. @Faheem, you’re welcome, and thanks for letting me know that it helped you.

1. Faheem Ur Rehman says:

Dear Debra, you deserve to be thanked for your excellent work and helpful articles.
I am looking for a solution to another requirement. I linked label of a graph to a cell which has a CONCATENATE formula to join contents of two cells. I want different font formatting (size and color) for two sections of the label. Kindly advise if it is possible with formulas.
I viewed your profile in Linkedin and have sent you request to join.
Best Regards
Faheem

2. @Faheem, thank you! You can’t change font formatting in a formula, but you could break the formula into multiple cells, based on the sections that you want coloured. Then, link those to separate text boxes on the chart, and format the text box font

17. Joel P says:

I have a formula that grabs data from page 1 in a workbook I have to create various printed results.
=IF(ISBLANK(DATA!\$C1),””,DATA!\$G\$1&DATA!\$C1&DATA!\$G\$2)
The result would be “AB12345C, where DATA!G1 would be AB, DATA!C1 would be 12345 and DATA!G2 would be C
I want to insert a space between the C1 and G2 cells so that the result in the cell is this…
AB
12345C
I have text wrapping on. I’ve tried to insert the “CHAR(10)” into various parts of the formula. Any questions?
PS. Reason being for so many \$ in the formula is that those cells are the prefix and suffix of a serial number generator I made, only one cell changes so this formula will be used multiple times with only one cell data varying.
Any idea?

1. Try setting vertical format to Justify on the cells that will display this information. Right Click -> Format Cells… -> Alignment -> Vertical = Justify.

18. Jeff says:

Doesnt work if you have an equation to continue the stacking of text in a single cell.

19. Santosh says:

Thanks, Char(10) worked…

20. Sandeep Gavara says:

CHAR(10) worked like a champ..Thanks a lot guys!!

21. Rich says:

great stuff – as always. Thank you Debra!

22. Clarke says:

I am trying to combine multiple lines into one cell.
example. I have a table with different commands
A1 [copy tftp startup-config]
B1 [192.168.1.1]
C1 [ ABC_192.168.1.2]
D1 [startup-config]
I want all of this information to end up in the E1 cell. The cell should look like this:
copy tftp startup-config
192.168.1.1
ABC_192.168.1.2
startup-config
How using the CHAR(10) function can I accomplish this? I want all of this information vertically so that I can enter it into a Cisco switch.