Line break character in Excel. How to make a line break in an Excel cell – all methods

Often people working in an Excel spreadsheet program are faced with a situation where they need to wrap a line. You can implement this simple procedure in a variety of ways. In the article, we will analyze in detail all the methods that allow you to transfer a line on the workspace of a spreadsheet document.

How to remove line breaks from cells in Excel 2013, 2010 and 2007

There are 3 methods to implement the removal of carriage returns from fields. Some of them implement replacement of line break characters. The options discussed below work the same in most versions of the spreadsheet editor.

Line wrapping in textual information occurs for a variety of reasons. Common reasons include things like using the Alt+Enter keyboard shortcut, as well as transferring text data from a web page to a spreadsheet program workspace. We need to remove the carriage return, since without this procedure it is impossible to implement a normal search for exact phrases.

Important! Initially, the phrases “Line feed” and “Carriage return” were used while working on printing machines and denoted 2 different actions. Personal computers were created taking into account the functions of printing machines.

Removing carriage returns manually

Let’s analyze the first method in detail.

• Advantage: fast execution.
• Cons: Lack of additional features.

Detailed instructions look like this:

1. We make a selection of all cells in which it is necessary to implement this operation or to replace characters.

2. Using the keyboard, press the key combination “Ctrl + H”. A window called “Find and Replace” appeared on the screen.
3. We set the pointer to the line “Find”. Using the keyboard, press the key combination “Ctrl + J”. There is a small dot in the line.
4. In the line “Replace with” we enter some value that will be inserted instead of carriage returns. Most often, a space is used, since it allows you to exclude the gluing of 2 adjacent phrases. To implement the removal of line wrapping, the “Replace with” line must not be filled with any information.

5. Using the LMB, click on “Replace All”. Ready! We have implemented carriage return removal.

Remove line breaks using Excel formulas

• Advantage: the ability to use a variety of formulas that perform the most complex verification of textual information in the selected field. For example, you can implement the removal of carriage returns, and then find unnecessary spaces.
• Disadvantage: you need to create an additional column, as well as perform a large number of manipulations.

Detailed instructions look like this:

1. Let’s implement the addition of an additional column at the end of the original information. In this example, it will be called “1 line”
2. In the 1st field of the additional column (C2), we drive in a formula that implements the removal or replacement of line breaks. Several formulas are used to carry out this operation. A formula suitable for use with combinations of carriage return and line feed looks like this: =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),””);CHAR(10),””).
3. A formula suitable for replacing a line break with some character looks like this: =TRIMSPACES(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),””);CHAR(10);”, “). It is worth noting that in this case there will be no merging of lines.
4. The formula to remove all non-printable characters from text data looks like this: =CLEAN(B2).

5. We copy the formula, and then paste it into each cell of the additional column.
6. Additionally, you can replace the original column with a new one, in which line breaks will be removed.
7. We make a selection of all cells located in column C. We hold down the “Ctrl + C” combination on the keyboard to implement copying of information.
8. We select field B2. Press the key combination “Shift + F10”. In the small list that appears, click LMB on the element that has the name “Insert”.
9. Let’s implement the removal of the auxiliary column.

Remove line breaks with VBA macro

• Advantage: creation occurs only 1 time. In the future, this macro can be used in other spreadsheet documents.
• Disadvantage: You need to understand how the VBA programming language works.

To implement this method, you need to get into the window for entering macros and enter the following code there:

Wrap text in a cell

The spreadsheet editor Excel allows you to transfer text information to the field. This is done so that text data is displayed on several lines. You can perform a setup procedure for each field so that the transfer of text data is carried out automatically. Additionally, you can implement a line break manually.

Automatic text wrapping

Let’s analyze in detail how to implement the automatic transfer of text values. The step by step algorithm looks like this:

1. We select the required cell.
2. In the “Home” subsection we find a block of commands called “Alignment”.
3. Using the LMB, select the “Move Text” element.

Important! The information contained in the cells will be transferred taking into account the width of the column. Editing the column width will automatically adjust text data wrapping. 

Adjust line height to display all text

Let’s analyze in detail how to implement the procedure for adjusting the line height to display all text information. Detailed instructions look like this:

1. We select the desired cells.
2. In the “Home” subsection we find a block of commands called “Cells”.
3. Using the LMB, select the “Format” element.
4. In the “Cell Size” box, you must perform one of the options described below. The first option – to automatically align the line height, click LMB on the “Auto-fit line height” element. The second option is to manually set the line height by clicking on the “Line height” element, and then entering the desired indicator into an empty line.

Inserting a line break

Let’s analyze in detail how to implement the procedure for entering a line break. Detailed instructions look like this:

1. By double-clicking LMB, we select the field into which we want to drive a line break. It is worth noting that you can select the required field, and then click on “F2”.
2. By double-clicking LMB, we select the place where the line break will be added. Press the Alt+Enter combination. Ready!

How to make a line break in an Excel cell with a formula

Often, spreadsheet editor users add a variety of charts and graphs to the workspace. Typically, this procedure requires line wrapping in the field’s text information. Let’s see in detail how to implement this moment.

Formula for line wrapping in Excel cells

For example, we have a histogram implemented in a spreadsheet program. The x-axis contains the names of employees, as well as information about their sales. This type of signature is very convenient, as it clearly shows the amount of work performed by employees.

It is very easy to implement this procedure. It is necessary to add the SYMBOL operator in place of the formula. It allows you to implement the generation of indicators in the fields for signing information in the diagram.

Of course, in the field, you can implement the line wrapping procedure anywhere, thanks to the combination of the Alt + Enter buttons. However, this method is inconvenient in cases where there is too much data.

How the CHAR function works when wrapping lines in a cell

The program uses codes from the ASCII character table. It contains the codes of characters displayed on the display in the OS. The tablet contains two hundred and fifty-five numbered codes.

A table editor user who knows these codes can use them in the CHAR operator to implement the insertion of any character. In the example discussed above, a line break is added, which is connected on both sides of the “&” between the indicators of fields C2 and A2. If the mode called “Move text” is not activated in the field, then the user will not notice the presence of a line break sign. This can be seen in the image below:

It is worth noting that on various charts, line breaks added using a formula will be displayed in a standard way. In other words, the text line will be split into 2 or more.

Division into columns by line break

If the user in the “Data” subsection selects the “Text by columns” element, then he will be able to implement the transfer of lines and the division of test information into several cells. The process is carried out using the Alt + Enter combination. In the “Wizard of text distribution by columns” box, you must check the box next to the inscription “other” and enter the combination “Ctrl + J”.

If you check the box next to the inscription “Consequent separators as one”, then you can implement the “collapse” of several line breaks in a row. At the end, click on “Next”. As a result, we will get:

Divide into lines by Alt + Enter through Power Query

There are situations when the user needs to divide multi-line text information not into columns, but into lines.

To implement this procedure, the Power Query add-in, which has appeared in the spreadsheet editor since 2016, is great. Detailed instructions look like this:

1. Using the “Ctrl + T” combination, we convert the source data into a “smart” plate. An alternative option is to move to the “Home” subsection and click LMB on the “Format as table” element.
2. Move to the “Data” subsection and click on the “From Table/Range” element. This operation will import the plate into the Power Query tool.

3. We select a column with multi-line text information. We move to the “Home” subsection. Expand the list of the “Split column” indicator and click LMB on the “By separator” element.

4. Click on “OK” to confirm the changes made. Ready!

Macro for division into lines by Alt+Enter

Let’s see how to implement this procedure using a special macro. We open VBA using the Alt + F11 key combination on the keyboard. In the window that appears, click “Insert”, and then “Module”. Here we add the following code:

We return to the workspace and select the fields in which the multiline information is located. Press the combination “Alt + F8” on the keyboard to activate the created macro.

Conclusions

Based on the text of the article, you can see that there are a huge number of ways to implement line wrapping in a spreadsheet document. You can perform this procedure using formulas, operators, special tools and macros. Each user will be able to choose the most convenient method for himself.