Line breaks in Excel: a new line in a cell, with a formula and gone again

A line break in a cell makes the text continue on a new line inside the same cell, the way pressing Enter does in a Word document. It is handy for addresses, long column headers and notes, where one cell has to hold several things without getting very wide. In Excel, Enter doesn't work that way. Press Enter and Excel saves what you typed and moves the cursor down to the next cell.

Here you'll see the shortcut for a line break on Windows, on a Mac and in Google Sheets, how to add line breaks with a formula, and how to get rid of them when they come in from another system. At the end you'll see how to count the lines in a cell and split a cell at its line breaks. Practice along the way in the exercises on joining text, where you write the formulas yourself in a spreadsheet and get an answer right away.

A line break in a cell with Alt+Enter

The quickest way is a shortcut:

1. Double-click the cell, or select it and press F2, so you are editing inside the cell.
2. Put the cursor where the new line should start.
3. Press Alt+Enter. The text after the cursor moves down to a new line.
4. Press Enter to save the cell.

You can also keep typing after Alt+Enter, so the next line follows right away. Type "12 Main Street", press Alt+Enter, type "Springfield", and press Enter. The cell now shows the address on two lines, and the row gets taller on its own so both lines are visible.

Step 1 is the one that matters. Alt+Enter only works while you are inside the cell, editing it. If the cell is only selected, with the green border around it, nothing happens, or Alt does something else entirely: it shows KeyTips on the ribbon. That is the most common reason people think the shortcut is broken.

On a Mac

On a Mac the shortcut is Option+Return. In some versions of Excel for Mac, Control+Option+Return works too. Both need you to be editing the cell, just as on Windows.

In Excel for the web and Google Sheets

In Excel for the web the shortcut is the same as on Windows: Alt+Enter. In Google Sheets, press Ctrl+Enter on Windows and Cmd+Enter on a Mac. Alt+Enter also works in Google Sheets on Windows. Be careful with Ctrl+Enter in Excel, where it means something else: it types the same entry into every selected cell at once.

Wrap Text: when you can't see the line break

When you add a line break with Alt+Enter, Excel turns on Wrap Text for the cell by itself. That setting is what makes Excel show the text on several lines. With Wrap Text off, the lines run together on one line, even though the line break is still there.

That usually happens in three situations:

• You add the line break with a formula. Excel doesn't turn on Wrap Text by itself when the line break comes from a formula.
• You paste text from somewhere else. An address copied from an email or a web page can carry line breaks that don't show.
• You copied the format from another cell. With Format Painter, for example, which copies Wrap Text along with everything else.

The fix is the same every time. Select the cells and click Wrap Text on the Home tab. If you still only see the first line, the row is too short. Double-click the line under the row number and the row fits its content.

Wrap Text also does something else: it breaks long text at the edge of the column, even without a line break. Those are two different things. A line break is a character inside the text, and it stays in the same place however wide the column is. Wrapping at the edge moves when you make the column wider or narrower. If a header has to break at a particular word, use a line break. If a long text only needs to fit in a narrow column, Wrap Text is enough.

A line break with a formula: CHAR(10)

A line break is a character just like a letter, a space or a comma. It is number 10 in the character table Excel uses, and CHAR gives you the character for a number. So =CHAR(10) gives a line break, and you can put it between two texts with &, the way you'd put a space between them:

The formula =A2&CHAR(10)&B2 reads: the text in A2, then a line break, then the text in B2. Remember to turn on Wrap Text for C2, or you'll see "12 Main StreetSpringfield, IL 62701" on one line.

The same pattern works for more than two parts. =A2&CHAR(10)&B2&CHAR(10)&C2 gives three lines, such as name, street and city on a mailing label. Text you type yourself goes in quotes: ="Reply by:"&CHAR(10)&B2 gives a fixed first line and the contents of B2 on the next.

If a number or a date is part of the text, it loses its format when it is joined to text. A date turns into a number like 46023. Wrap the number in TEXT so it looks right: =A2&CHAR(10)&TEXT(B2,"mmm d, yyyy") gives the name on the first line and a date like "Jan 1, 2026" on the second.

Many lines at once with TEXTJOIN

To gather a whole list into one cell with a line break between each item, & quickly gets long. TEXTJOIN takes a separator and a range and does it in one go:

The first argument is the separator, here CHAR(10). The second, TRUE, means empty cells are skipped, so you don't get a blank line when one of the cells is empty. The third is the range to join. TEXTJOIN is in Excel 2019 and later and in Excel for Microsoft 365. Practice it in the exercise on joining an address.

CHAR(10) on a Mac and in Google Sheets

CHAR(10) works the same way in Excel for Mac. Very old versions of Excel for Mac used character 13 for a line break, but today's Excel doesn't. In Google Sheets the function is also called CHAR, and the formula is the same: =A2&CHAR(10)&B2. Google Sheets turns on wrapping with the Text wrapping button on the toolbar.

Remove line breaks in Excel

Line breaks are useful when you put them there yourself. When they come along in data from another system, they are usually in the way. An export from a CRM or an online store can have addresses, descriptions or comments with line breaks inside the cells. That makes the rows tall and hard to read, and it breaks lookups and comparisons: "Main Street" with a line break after it is not the same as "Main Street" without one.

There are three ways to get rid of them. Choose based on whether you want to keep the original text.

With SUBSTITUTE: keep the original

SUBSTITUTE swaps one piece of text for another. Swap CHAR(10) for a space, a comma or nothing at all:

The formula =SUBSTITUTE(A2,CHAR(10),", ") replaces each line break with a comma and a space. For just a space, write =SUBSTITUTE(A2,CHAR(10)," "), and to remove the line break entirely, write =SUBSTITUTE(A2,CHAR(10),""). The formula goes in a new column, so the original text stays untouched, and you can copy it down every row. Afterward you can copy the column and paste it as values if you want to drop the formulas. Try SUBSTITUTE in the exercise on removing characters, where it removes dashes rather than line breaks, but the formula is the same.

If double spaces are left over, for example because there was a space before the line break, wrap TRIM around it: =TRIM(SUBSTITUTE(A2,CHAR(10)," ")). It removes spaces at the start and end and turns double spaces into single ones.

With Find and Replace: right in the cells

To change the cells themselves without a formula, use Find and Replace:

1. Select the cells to fix. If you select only one cell, Excel searches the whole sheet.
2. Press Ctrl+H, or choose Home > Find & Select > Replace.
3. Click in the Find what box and press Ctrl+J. The box looks empty, but it holds a line break. Sometimes you can see a small blinking dot.
4. Type what the line break should become in the Replace with box, such as a space. Leave it empty if the line break should simply go.
5. Click Replace All.

Ctrl+J works because J is the tenth letter of the alphabet, and character 10 is the line break. It is an old trick from the days of teletype machines, and it still works in Excel. Find and Replace changes the original cells, so try it on a copy of the sheet first, or be ready with Undo.

Once you've used Ctrl+J, the line break stays in the Find what box the next time you open Find and Replace. If you then search for something else and find nothing, clear the box completely with Delete and Backspace before you type the new search.

With CLEAN: every invisible character

Data from other systems can carry more kinds of invisible characters than line breaks. Windows traditionally uses two characters for a new line: character 13, the carriage return, followed by character 10. So when a text file from Windows comes into Excel, a character 13 can be left over after you remove character 10, and you won't notice until a lookup finds nothing.

CLEAN removes every character numbered below 32, including both characters of a Windows line break. =CLEAN(A2) returns the text without them. Watch out for one thing: CLEAN removes the characters without putting anything in their place. If "12 Main Street" and "Springfield" are on separate lines, you get "12 Main StreetSpringfield". For a space between the parts, use SUBSTITUTE first and CLEAN after: =CLEAN(SUBSTITUTE(A2,CHAR(10)," ")).

Work with the text in a cell with line breaks

Sometimes the line breaks have to stay, and you only need part of the text. Since a line break is an ordinary character, you can find it with FIND and use it to cut the text apart.

The first line

FIND gives the position of the first line break in the text. Everything before that position is the first line, and LEFT takes exactly those characters:

=LEFT(A2,FIND(CHAR(10),A2)-1) takes the characters to the left of the line break. The minus 1 is there because the line break itself shouldn't come along. If the cell has no line break, FIND returns #VALUE!. To get the whole text in that case, wrap the formula in IFERROR: =IFERROR(LEFT(A2,FIND(CHAR(10),A2)-1),A2).

In Excel for Microsoft 365 it's even easier with TEXTBEFORE, which takes everything before a given text: =TEXTBEFORE(A2,CHAR(10)). Its opposite, TEXTAFTER, gives everything after the first line break: =TEXTAFTER(A2,CHAR(10)).

Count the lines in a cell

Excel has no function that counts lines, but you can work it out. Take the length of the text, subtract the length of the same text without line breaks, and you have the number of line breaks. Add one for the number of lines:

=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1 works because each line break is one character. If the cell is empty, the formula returns 1 even though there are no lines. For 0 on an empty cell, write =IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1). It's the same trick as counting words by counting spaces, which you can practice in the exercise on counting the words in a cell.

Split a cell at its line breaks

If a cell holds several lines that each belong in their own cell, TEXTSPLIT in Excel for Microsoft 365 can do it: =TEXTSPLIT(A2,CHAR(10)) sends each line into its own column. To put the lines under each other instead, give the line break as the third argument, the row delimiter: =TEXTSPLIT(A2,,CHAR(10)).

In older versions of Excel, use Text to Columns on the Data tab. Choose Delimited, check Other, click in the box next to it and press Ctrl+J. Excel then splits the cell at each line break and puts the parts in the columns to the right. Text to Columns changes the selected cells and writes over the cells to the right, so make sure there are enough empty columns.

Line breaks in headers and tables

Line breaks are especially useful in column headers. A header like "Sales before tax" over a narrow column of numbers makes the column wide, even though the numbers take little room. With a line break after "Sales", the header sits on two lines and the column can be as narrow as the numbers. That's a better fix than to merge cells, because every cell stays separate and the table can still be sorted and filtered.

A few things are worth knowing when you use line breaks in data:

• Lookups need an exact match. VLOOKUP, XLOOKUP and SUMIF tell "North" apart from "North" with a line break after it. If the lookup values come from a different source than the table, clean up the line breaks first.
• Pivot tables show the line break. A category with a line break shows on several lines in the pivot table, and two categories that differ only by a line break become two rows.
• CSV files can break. When you save a sheet with line breaks in its cells as CSV, those cells go in quotes, so the line break stays inside the cell. Some other programs don't understand that and split the row in two. Remove the line breaks before you export if you're not sure about the program on the other end.
• The row height follows along. Excel makes the row taller when you add a line break. Remove the line break again and the row doesn't always shrink on its own. Double-click the line under the row number to fit it.

When the line break doesn't work

These are the problems people run into most, and what to do about them:

• Alt+Enter moves down to the next cell. You weren't in edit mode. Double-click the cell or press F2 first.
• Alt+Enter shows letters on the ribbon. Those are KeyTips, and they appear when you press Alt alone without being in the cell. Press Esc, go into the cell and try again.
• The formula with CHAR(10) sits on one line. Wrap Text is off. Turn it on from the Home tab.
• A strange character appears instead of a line break. That usually happens when text from Windows is opened on a Mac, or when a program shows character 13 as a box. Remove the characters with CLEAN, or with SUBSTITUTE and CHAR(13).
• Find and Replace finds nothing. The Find what box may still hold an old line break or a space. Clear the box and press Ctrl+J again.
• Only the first line shows. The row is too short. Double-click the line under the row number.

Practice

Line breaks are quick to learn. The hard part is rarely the shortcut. It's remembering that the character is there even when you can't see it. When a lookup suddenly can't find a value you can plainly see in the table, a hidden line break or an extra space is one of the first things to check. LEN gives it away quickly: if =LEN(A2) returns a number larger than the characters you can see, something invisible is in the cell.

The formulas around line breaks are the same ones you use for all kinds of text: & and TEXTJOIN to join, SUBSTITUTE to swap, and FIND and LEFT to cut out. Start with joining a first and last name, move on to joining an address with TEXTJOIN, and finish with removing extra spaces. Each exercise is a small spreadsheet where you write the formula yourself and find out right away whether it works.

For more shortcuts like Alt+Enter, see the most useful Excel shortcuts, and to clean up a whole sheet of data from another system, read the guide on how to clean data in Excel.