How do you add Find and Replace?

Tips > Data Entry > Line Breaks

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

Video: Excel Line Breaks

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..

Video Timeline

• 00:00 Introduction
• 00:06 Create a Line Break
• 00:17 Wrap Text
• 00:23 Remove Line Breaks
• 00:33 Find and Replace
• 00:44 Find What
• 01:01 Replace With
• 01:14 Find Next
• 01:27 Line Break Shortcuts

The full video transcript is available, further down on this page.

Add Line Break in Cell Text

To show text on separate lines within a single cell, you can add a line break.

• In this screen shot, there are two words in cell B2 and cell B3.
• Each cell has a line break after the first word, so the second word is on a new line of text inside the cell

The steps for adding a line break are below the screen shot.

Add a line break when typing in a cell

1. Select the cell
2. In the formula bar, click where you want the line break
   

3. On the keyboard, press Alt + Enter, to add a line break
4. Press Enter, to complete the formula

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:

1. Select the cell
2. In the formula bar, click at the start of the second line
3. To remove the line break, press the Backspace key
4. If necessary, type a space character, to separate the words in the cell
5. Press Enter, to complete the formula change

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:

• A line break (line feed) is character 10 in the ASCII characters
• The Ctrl + J shortcut is the original ASCII control code for character 10

Notes for Excel for Mac

• If you are using Excel for Mac, the Ctrl + J shortcut does NOT work in the Find and Replace dialog box.
• As a workaround, you can try the suggestions in this thread in Microsoft's Excel Forum.

Steps to Find and Replace Line Break

To find line breaks in an Excel worksheet, follow these steps:

1. Select the cells that you want to search
2. On the keyboard, press Ctrl + F to open the Find and Replace dialog box, with the Find tab active
3. Click in the Find What box
4. On the keyboard, press Ctrl + J to enter the line break character
• NOTE: Nothing will appear in the Find What box
5. Click Find Next or Find All, to find the cells with line breaks.

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:

1. Select the cells that you want to search
2. On the keyboard, press Ctrl + H to open the Find and Replace dialog box, with the Replace tab active
3. Click in the Find What box
4. On the keyboard, press Ctrl + J to enter the line break character -- NOTE: Nothing will appear in the Find What box
5. Press the Tab key on the keyboard, to move to the Replace With box
6. Type a space character
7. Click Find Next or Find All, to find the cells with line breaks.
8. Click Replace or Replace All, to replace the line breaks with space characters.

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:

• Tthere is still a hidden Ctrl+J character in the Find What box, left over from your first search
• When you enter another Ctrl+J, Excel looks for places wehre there are TWO line breaks.
• It probably won't find any cells with 2 line breaks, side by side

So, if Excel says it can't find line breaks on a second Find and Replace, these steps to fix the problem:

1. Press Ctrl+H to open Find and Replace
2. Click on the Replace tab -- you'll see a light border around the word
3. Press the Tab key -- that selects everything in the Find What box
4. Type Ctrl+J -- that replaces anything in the Find What box
5. Then click click one of the Find or Replace buttons at the bottom of the form

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.

• If Ctrl + J does not work, press the Alt key, and type 0010 on the number keypad (do not use the numbers above the letters)

• If the Excel file has never been saved, try saving the file before doing this find and replace

• Try doing this find and replace when you first open Excel, before using the Find box for any other searches.

Line Breaks in Excel Formulas

There are two ways you can use line breaks in Excel formulas:

1. Use the Alt+Enter shortcut, to create line breaks in the formula bar
• Those line breaks will NOT appear in the formula result
• These line breaks could help you with formula troubleshooting
2. Use the CHAR function in the formula, to add a line break in the cell text
• Those line breaks WILL appear in the formula result
• These line breaks could make formula results easier to read

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.

• NOTE: This will NOT add line breaks to the formula result in the worksheet cell.
  • See the next section for steps to do that.

To add line breaks in the Formula Bar, follow these steps:

• Click on the cell that contains the long, complicated formula
• Drag the bottom of the Formula Bar down, to make it taller
• Click somewhere in the formula, where you'd like to add a line break
  • Be sure to select a spot that will not affect the formula's calculation!
  • For example, click after a comma, or before a function name
  • Do NOT click inside a range reference
• To add a line break in the formula, press Alt + Enter
• Add more line breaks, if needed, so you can read the formula more easily, for troubleshooting
• When you're finished, press the Enter key, to complete the formula change
  • OR, press Ctrl+Shift+Enter, if you were adding line breaks to an array-entered formula

Add Line Break in Formula Result

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.

• This is Excel's text concatenation operator
• See details and videos in the & Operator section below
• This is quicker and easier than the CONCATENATE function

Excel 2019 and Later Versions, Excel 365

For newer versions of Excel (2019 and later), you can use the new TEXTJOIN function

• This new function makes it easy to combine several cells.
• See details and videos in the TEXTJOIN function section below
• This function has more options than the new CONCAT function - I don't use it

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

• Select cell C2, where the formula will calculate the full name
• Type an = (equal sign) to start the formula
• Click on the first name cell (B2), to add its cell reference to the formula
• Type the & operator
• Type CHAR(10) to create a line break in the formula result
  • 10 is the number code for a line break
• Type the & operator
• Click on the last name cell (A2), to add its cell reference to the formula
• The completed formula is : =B2 & CHAR(10) & A2
  • Note: If the formula is in a named Excel table, you'll see structured table references (column names), instead of normal cell references.
  • =[@First] & CHAR(10) & [@Last]
• Press Enter to complete the formula -- the result does not show the line break yet, so continue to the next section, below

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:

• Select the cell where you entered the formula
• On the Excel Ribbon, go to the Home tab
• In the Alignment group, click the Wrap Text button

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.

• The wrapped cells with line breaks, in column C, have expanded the row height
• First and Last names, in columns A and B, are at the bottom of their cells
• This makes it hard to read a row, because you're reading across the bottom, and then your eyes have to jump to the top in column C

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.

TEXTJOIN Function

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 A4, there is an order number
• In cell B4, there is a customer name

In cell C4, a TEXTJOIN formula will combine those two values, with a line break between them

TEXTJOIN Arguments

The TEXTJOIN function has 3 required arguments in its syntax:

1. delimiter - A text string to separate the joined text items.
• Our formula will use a line break: CHAR(10)
2. ignore_empty - ignore blank cells (TRUE), or include them (FALSE)
• Our formula will ignore empty cells - TRUE
3. text1 - The text items to join.
   • Join the values in range A4:B4

TEXTJOIN Formula

In cell C4, the following formula combines values from A4 and B4, with a line break between them

• =TEXTJOIN(CHAR(10),TRUE,A4:B4)

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.

• separates item details with commas
• adds line break after each item, to create a new line of text
• all items are listed within a single cell

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.

• There are multiple rows for each order
• Each item purchased in the order is on a separate row.

Order Summary Sheet

The sample workbook also has an Order Summary sheet, where you can:

• Select an Order ID in cell B3, from a data validation drop down list
• See all the items from the selected order ID, in cell B4

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.

• =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

Here's how the commas and line breaks are added:

• Comma separators are in the TEXTJOIN function: TEXTJOIN(", "
• The line breaks are created with the &CHAR(10) code.
• At the end of the formula, SUBSTITUTE function removes the extra "comma space"
  • CHAR(10) & ", ",CHAR(10))

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.

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?

• First, we're going to select the area that we want to change.
• Let's head to Find and Replace.
• You can get there, either by:
  • going to the magnifying glass on your Home ribbon,
  • or by using the keyboard shortcut: Ctrl + H

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.

Replace With

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.

• Whatever you want to see in the cell, type it here. I'm going to use a space.
• Hit Find next, and we'll see the first instance
• Then 'Replace' to make the change.
• You can then click, Replace All and see that it found two more.

Those are your shortcuts for line breaks -- make, find, and replace them.

More Function Tutorials
		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.

How do you add Find and Replace in word?

Where is Find and Replace in word 2022?

How do you add a subscript in Find and Replace?

Where is the Find and Replace tab in word?

What is the code for Find and Replace?

Where is find and replace in Excel?