How to Do a Line Break in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Line breaks are important to improve visuals in cells. It can also be used to store more than one piece of data if needed in a single cell. Whatever your cause might be, here I will help you with how to do a line break in Excel.

Sometimes we need to store multiple lines of information in a single cell. For example, a person’s details. So that we can understand the data better, again if there are too many words in a single cell, it’s better to start with new lines or paragraphs. In such cases, you must know how to add line breaks.

Overview image on How to Do a Line Break in Excel with 3 formulas

The above overview image shows 3 formulas for inserting a line break in Excel. Follow along to know more in detail.


Download Practice Workbook

You can download the workbook with all the sheets and examples used for this demonstration below. Download and give it a try.


How to Do a Line Break in Excel: 3 Easy Ways

Here we’ll cover the keyboard shortcut method, the Find and Replace command, and formulas to do a line break in Excel.


1. How to Do a Line Break in Excel Through Keyboard Shortcut

press Alt and Enter to insert a line break

Double-click on the desired cell and place your cursor where you want the line break. For Windows users, press Alt and Enter simultaneously. If you are using Mac, press Control + Option + Enter.

cell outputs with line breaks

After repeating the same process where the line breaks are needed, you’ll get the output as shown in the above image.

Note:

If you fail to see all the contents in a cell clearly, adjust the row heights manually. Or you can double-click the row borders (observe the cursor shape in the image) to adjust automatically.


2. Use Find and Replace Command to Add a Line Break After a Specific Character

You can also use the Find and Replace command in Excel to do a line break. With this tool, you can replace a character or a set of characters with other ones. To break lines we can suitably use a particular character to replace it with a line break.

press Ctrl and H keys after selecting the range

Select C5:C11 and press Ctrl and H keys together.

input necessary fields in find and replace box

The Find and Replace dialog box pops out. Insert:

Find what >> A space

Replace with >> Press Ctrl + J

Here, Ctrl + J is the shortcut for character 10, a line break, in the ASCII code.

Lastly, press Replace All.

replaced spaces with line breaks

Hence, the cells with desired line breaks will appear.

Read More: Find and Replace Line Breaks in Excel


3. Insert Line Break with CHAR Function in Excel

The CHAR function takes a numerical argument and returns the character which the number represents. CHAR(10) indicates a line break in Excel formulas. We’ll combine it with other functions to add a line break in Excel.


3.1 Use Ampersand Sign to Start New Line

combine Ampersand and CHAR for line break

To combine CHAR(10) with the ampersand (&) sign, use the formula in cell E5:

=B5 & CHAR(10) & C5 & CHAR(10) & D5

Apply AutoFill and you’ll get the cells with line breaks.


3.2 Insert CONCAT Function to Do a Line Break

The CONCAT function takes several arguments and concatenates all the values in them.

combine CONCAT and CHAR for line break

In cell E5, insert the formula:

=CONCAT(B5, CHAR(10), C5, CHAR(10), D5)

Fill the rest using AutoFill.

Here, the formula concatenates the values in the range of cells B5:D5 with a line break in between them.


3.3 Add Line Break with TEXTJOIN Function

The TEXTJOIN function joins texts with a delimiter in between them.

combine TEXTJOIN and CHAR for line break

Use the formula in cell E5:

=TEXTJOIN(CHAR(10), TRUE, B5:D5)

Then apply AutoFill to get other cells with line breaks.

The formula joins the values within the range of cells B5:D5 with a line break after each cell value and ignores all the empty values.

Use Defined Name Instead of CHAR(10) in Excel Formulas

As we’re using CHAR(10) repeatedly in the formulas, we can set an alternative short name for it.

select define name

Go to the Formulas tab > click Defined Names drop-down > select Define Name.

create LB for CHAR(10)

In the pop-out dialog box, input

Name > LB

Scope > Workbook

Refers to > =CHAR(10)

Press OK. Now you can type LB instead of CHAR(10) in the formulas. For example,

=B5 & LB & C5 & LB & D5

How to Enable Wrap Text Feature in Excel (to Line Break Automatically)

The Wrap Text feature generally helps to avoid the cell content overlapping on the next cell and tries to give a clearer view of what is inside the cell. It cannot insert line breaks where we need them, but it does line break at the point where the text exceeds the column width of the cell.

cells without wrap text

The above image shows the cell contents in the B column getting spilled over onto the neighboring cells.

wrap text enabled

Select the B5:B11 range and go to the Home tab. Click Argument drop-down > select Wrap Text. Thus, the cell contents get adjusted by themselves to fit the column width and show the contents clearly by inserting line breaks where necessary.

Read More: How to Make Excel Go to Next Line Automatically


How to Create a New Line in Formula Bar in Excel

press Alt and Enter in formula bar

Select the cell and go to the formula bar. Click and place the cursor where we need to do a line break.  For Windows users, press Alt and Enter. For Mac users, press Control + Option + Enter.

formula with line breaks

This way you can get line breaks in the formula.


How to Remove Line Breaks in Excel

press Ctrl and H keys after selecting the range

To remove line breaks, select C5:C11 and press Ctrl and H keys together.

put necessary fields in find and replace box

The Find and Replace dialog box pops out. Input:

Find what >> Press Ctrl + J

Replace with >> A space

Lastly, press Replace All.

replaced line breaks with spaces

Adjust column widths and you’ll see the cell contents in a single line.


Things to Remember

1. Enable the Wrap Text feature when using & sign and functions to see the cell contents clearly. It’s a must for multiple lines to appear in a cell.

2. Adjust column widths and row heights when necessary to view all the cell contents.

3. The code for a line break on Windows is 10, so use CHAR(10). For Mac, it’s 13, so use CHAR(13).


Conclusion

These were all the methods on how to do a line break in Excel.

For a quick line break, use the keyboard shortcut by pressing Alt and Enter.

Apply the formula for doing line breaks in multiple cells at a time. Hope you found this guide informative and helpful. If you have any questions or suggestions let us know below. For more detailed guides like this, visit Exceldemy.com.


Related Articles


<< Go Back to Line Break in Excel | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo