How to add line breaks in Excel cells or in a formula. See how to find and replace line breaks in Excel cells. Watch short video, see written steps and screen shots, download free workbook
Author: Debra Dalgleish
In this short video, Sarah shows how to add a manual line break in a cell. Then, see how to find line breaks in a group of cells on the worksheet, and replace with space character. Written steps are below the video..
The full video transcript is available, further down on this page.
To show text on separate lines within a single cell, you can add a line break.
The steps for adding a line break are below the screen shot.
Add a line break when typing in a cell
The cell will automatically be formatted with Wrap Text, and you might need to widen the column.
Remove Line Breaks
To create a line break in a cell, you simply use the keyboard shortcut, Alt + Enter, as described in the previous section.
But how can you find and replace line breaks in Excel? How can you get rid of that Alt+Enter that you put into the cell
In the sections below, see how to remove a line break from a individual cells, or from a group of cells
Remove a Single Line Break
If you just want to remove a line break from a single cell, follow these steps:
Find and Replace Multiple Line Breaks
To find specific text in Excel, you can use Ctrl + F to open the Find and Replace dialog box.
However, if you try to type Alt + Enter in the Find What box, you’ll just hear a beep from your computer. Excel won’t let you put that shortcut into the Find What box.
Use This Shortcut Instead
Instead of using Alt + Enter, in the Find What bos, you need to use a different shortcut to find a line break: Ctrl + J
You might be asking, "Why does Ctrl+J work?"
Here's the reason for that:
Notes for Excel for Mac
Steps to Find and Replace Line Break
To find line breaks in an Excel worksheet, follow these steps:
NOTE: See the troubleshooting tips below, if Excel can't find line breaks
Replace the Line Breaks
After you find a line break, you can replace it with a different character, such as a space character.
To replace a line break with a space character:
Find and Replace Troubleshooting Tip #1
If you try to find line breaks a second time, Excel might say it can't find any.
That usually happens because:
So, if Excel says it can't find line breaks on a second Find and Replace, these steps to fix the problem:
More Find and Replace Troubleshooting Tips
Did you try the troubleshooting tip above, and Excel still won't fine and replace the line breaks?
Here are a few tips and troubleshooting suggestions, to use while you find and replace line breaks in Excel.
Line Breaks in Excel Formulas
There are two ways you can use line breaks in Excel formulas:
The steps for both methods of adding line breaks are shown in the sections below
Add Line Break in Formula Bar
If you are troubleshooting a long Excel formula, you can use the Alt + Enter shortcut, to make the complext formula easier to read in the formula bar.
To add line breaks in the Formula Bar, follow these steps:
To add a line break in an Excel formula's result, you'll the Excel CHAR function, and combine that function with other parts of the formula.
Two Ways to Combine Text
There are two formula methods that I use to combine a line break with text values in Excel.
With both methods, you'll use the CHAR function, with character code 10 as its argument, to create a line break - CHAR(10). We use that cahracter number because a line break (line feed) is character 10 in the ASCII characters.
Older Versions of Excel
In older versions of Excel, or if you are not sure what version your co-workers are using, it's best to use the & (ampersand) operator. It will work in all versions of Excel.
Excel 2019 and Later Versions, Excel 365
For newer versions of Excel (2019 and later), you can use the new TEXTJOIN function
See many more examples of combining text and formatted numbers, on the Combine Cells in Excel page.
Add Line Break with & Operator
To add a line break to a formula's result, you can use the Excel CHAR function, and combine it with the other parts of the formula..
Follow the steps below, to add the CHAR function, with character code 10 as its argument, to create a line break in a formula's result.
In this example, the formula result will show the first name from column B, then a line break, and then the last name, from column A
Format the Formula Cell
When you use CHAR to create a line break in a formula, the cell might NOT show the line break right away. For line breaks created inside formulas, Excel does not automatically turn on the Wrap Text setting.
Follow these steps fix the wrap text setting:
After that formatting change, the line breaks show correctly in the cell
Change Cell Alignment
Another finishing touch is to change the vertical alignment for all cells.
By default, worksheet cells are bottom-aligned, as you can see in the screen shot above.
To make the data easier to read, select all the cells, and change their vertical alignment to Top
After that change, your eyes flow better, across the top of each row, as you read it.
In Excel for Office 365, or Excel 2019 and later versions, you can use the TEXTJOIN function to combine text from multiple ranges, quickly and easily. You can specify a separator for the joined items, such as a comma, space, or a line break.
If you haven't used this new function before, watch this short video , to see a couple of TEXTJOIN examples.
Below the video, there are 2 examples, that show how to add a line break, when using TEXTJOIN.
Note: For more TEXTJOIN details and videos, and many more examples for using the TEXTJOIN function, go to the Combine Cells in Excel page.
TEXTJOIN with Line Break
In this example, for Excel 365, the values from two cells are combined, with a line break separating the values, using the new TEXTJOIN function.
In cell C4, a TEXTJOIN formula will combine those two values, with a line break between them
The TEXTJOIN function has 3 required arguments in its syntax:
In cell C4, the following formula combines values from A4 and B4, with a line break between them
Note: The Wrap Text format was applied to the OrderCust formula results, in column C.
TEXTJOIN - Comma and Line Breaks
In this example, for Excel 365, TEXTJOIN and FILTER function are combined, in a complex formula.
The formula result has comma-separated item details, and a line break after each item.
Note: You can get this sample file (TEXTJOIN Line Breaks) in the Download section below
Sales Order Items
In the sample workbook, there is a named Excel table, Sales_Data, where all the order details are stored.
Order Summary Sheet
The sample workbook also has an Order Summary sheet, where you can:
In cell B4, a complex formula combines the new TEXTJOIN and FILTER function with three of the older Excel functions - SUBSTITUTE, CHOOSE and CHAR.
TEXTJOIN and FILTER Formula
Here is the TEXTJOIN / FILTER formula in cell B4, which lists all the items from the selected order.
Note: The formula is colour coded to show the different functions.
Here's how the commas and line breaks are added:
Note: For full details on how this formula works, and many more examples for using the TEXTJOIN function, go to the Combine Cells in Excel page.
Combine Basic: The workbook has the Combine text examples with the line break and the & operator, and number formatting. The zipped file is in xlsx format, and does not contain macros.
TEXTJOIN Line Breaks: This workbook has the TEXTJOIN with Line Breaks example, which works in Excel for Microsoft 365. The zipped file is in xlsx format, and does not contain macros.
Video Transcript: Line Breaks
Here is the full transcript for the Video: Line Breaks, shown above.
Create a Line Break
Line breaks can be a great way to format information in a cell. Hi, this is Sarah from Contextures.com.
To create one, you just need to use an easy keyboard shortcut: Alt + Enter
I already have some in this document and I'm just going to add another. As a bonus, you'll see the cell is automatically formatted as Wrap Text.
Remove Line Breaks
What if you decide this is no longer what you want?
Make sure you're on the Replace tab and we'll start with Find what.
Now, if we put in the shortcut we used earlier Alt + Enter, you're just going to get rejected.
So we need to use the shortcut: Ctrl + J
Now you're not going to see anything, but trust that it's there.
Hit Tab to move down to Replace with.
Now, depending on how you want your data to look, you could replace with a space, a comma, dash.
Those are your shortcuts for line breaks -- make, find, and replace them.
How to Sum in Excel
Examples show how to sum in Microsoft Excel, with a simple SUM function, or formulas that sum based on one or more criteria. See how to fix formula problems, and for more power, use SUMIF, SUMFS, SUMPRODUCT, SUBTOTAL or AGGREGATE. Step-by-step videos, written instructions, sample files to download.
Split or Reverse Names
Quickly split first and last names into separate cells, with Excel tricks or formulas. See how to reverse order of names, from "Smith, Mary" to "Mary Smith".
Step-by-step videos, written instructions, sample files to download.
Last updated: July 25, 2022 10:49 AM