Move to a new line in an excel cell. Wrap lines in one cell. How to specify multiple lines of text without creating paragraphs

It is often necessary to wrap text inside one Excel cell to a new line. That is, move the text along the lines inside one cell as indicated in the picture. If, after entering the first part of the text, you simply press the ENTER key, the cursor will be moved to the next line, but to a different cell, and we need a transfer in the same cell.

This is very common task and it can be solved very simply - to move text to a new line inside one Excel cell, you need to click ALT+ENTER(hold down the ALT key, then without releasing it, press the ENTER key)

How to Wrap Text on a New Line in Excel Using a Formula

Sometimes you need to make a line break not just once, but using functions in Excel. Like in this example in the figure. We enter the first name, last name and patronymic and it is automatically collected in cell A6

In the window that opens, in the “Alignment” tab, you must check the box next to “Word wrap” as indicated in the picture, otherwise line wrapping in Excel will not be displayed correctly using formulas.

How to replace a hyphen in Excel with another character and back using a formula

Can change the hyphen symbol to any other character, for example on a space, using the text function SUBSTITUTE in Excel

Let's take an example of what is in the picture above. So, in cell B1 we write the SUBSTITUTE function:

SUBSTITUTE(A1,CHAR(10), " ")

A1 is our text with a line break;
CHAR(10) is a line break (we looked at this a little higher in this article);
" " is a space because we are changing the line break to a space

If you need to do the opposite operation - replace the space with a hyphen (symbol), then the function will look like this:

SUBSTITUTE(A1; " ";CHAR(10))

Let me remind you that in order for line breaks to be reflected correctly, you must specify “Wrap across lines” in the cell properties, in the “Alignment” section.

How to change the hyphen to a space and back in Excel using SEARCH - REPLACE

There are times when formulas are inconvenient to use and replacements need to be made quickly. To do this, we will use Search and Replace. Select our text and press CTRL+H, the following window will appear.

If we need to change the line break to a space, then in the “Find” line we need to enter a line break, for this stand in the “Find” field, then press the ALT key, without releasing it, type 010 on the keyboard - this is a line break code, it will not be visible in this field.

After that, in the “Replace with” field, enter a space or any other character that you need to change to and click “Replace” or “Replace All”.

By the way, this is implemented more clearly in Word.

If you need to change the line break character to a space, then in the “Find” field you need to specify special code"Line break", which is indicated as ^l
In the “Replace with:” field, you just need to make a space and click on “Replace” or “Replace All”.

You can change not only line breaks, but also other special characters, to get their corresponding code, you need to click on the “More >>”, “Special” button and select the code you need. I remind you that this function only exists in Word, these symbols will not work in Excel.

How to change line break to space or vice versa in Excel using VBA

Let's look at an example for selected cells. That is, we select the required cells and run the macro

1. Change spaces to hyphens in selected cells using VBA

Sub SpacesToHyphens()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(32) , Chr(10) )
Next
End Sub

2. Change hyphens to spaces in selected cells using VBA

Sub WrapsToSpaces()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(10) , Chr(32) )
Next
End Sub

The code is very simple: Chr (10) is a line break, Chr (32) is a space. If you need to change to any other symbol, then simply replace the code number corresponding to the required symbol.

Character codes for Excel

Below in the picture are indicated various symbols and their corresponding codes, several columns are a different font. To enlarge the image, click on the picture.

How to make a line break in an Excel cell? Options for hyphenating part of a word.
Inexperienced users do the transfer as in Word, that is, they press the enter key and here, instead of transferring, they simply move to another cell. However, you should know that this should be done using the keyboard by pressing the alt and enter keys simultaneously. This is one of the most simple ways. However, when constantly working with excel documents large volume, this method is not very effective.

Let's look at how you can use the formatting function to simplify the task of wrapping text in one cell.
This method has a small condition for those who will use it. The fact is that it is convenient if there is no accuracy in the transfer of words or parts of a word. That is, this option guarantees transfer only within the cell, but does not guarantee that you can use it to maintain accuracy in the transfer of certain words or letters. We will describe how to do this in practice. Select the required cell using the cursor. Then press the right mouse button and you will see a menu. In it we look for the cell format

The next step is to check “wrap by words” in the menu that opens. Check the box and press OK to confirm your actions.

After the manipulations described above, you can safely work in the cell with the text you need, and it will automatically be subject to forced word wrapping. This method helps you work with texts using formulas more efficiently. Of course, it all depends on your tasks. For example, you need this cell format not only for a specific cell, but for a larger array of cells. This is easy to do if you select the desired array or sheet and use the cell format function to set the above-described “word wrap” function. However, you should remember that words will be automatically wrapped only if the word is at the cell border. The default dimensions of cells are not large in height. AND this method formatting expands the boundaries on its own. If this does not happen, you should increase the height or width of the cell yourself.
Formulas
Let's touch on another way to break a line in a cell. This can also be done using a formula. Excel has a set of functions for working with text. We will use one of these functions. Let's say your cells are already formatted as described in option two. But you need a certain word or phrase to be transferred. And usually this option is used if the words are on other sheets, or part of some formula. We solve this problem as follows: select the cell in which it is necessary to make a specific transfer of a word or phrase, and enter the formula into it: (concatenate (text1); symbol (4); text2) The text in this formula implies that you need to insert a word, or phrase. So you enter the two words you need, indicate which character you need to start processing the text with.

Words in this formula must be placed in quotation marks, and then the formula itself will carry out the transfer of the parameter you need for you.

You only confirm by pressing enter that you agree to the changes.
All of the methods described above allow you to wrap words in a cell, and each of them is suitable for solving certain, specific problems. The two methods are quite easy and easy to remember. The third one takes some time. But it is also intended to solve narrower problems when transferring text.

When creating graphs and charts in Excel, there is often a need to make a line break in the text of a cell and more. For example, to get a better visual effect for the readability of data labels on graphs and charts.

Formula for line breaks in Excel cells

Consider the example below in the figure with a histogram created in Excel. The X-axis labels contain salesperson names and sales data. This example of data labeling on a histogram is always useful when the text needs to be compactly arranged so as not to obscure the elements of the histogram.

Secret this decision very simple and elementary. You just need to add the CHAR function at the right place in the formula that generates the values in the cells to label the histogram data.

Of course, you can break a line anywhere in a cell using the ALT+Enter key combination. But if there are dozens of such cells, it is better to automate this process using the simple formula above.

How does the CHAR function work when breaking lines in a cell?

MS Excel actively uses codes from the ASCII character table. It contains all the character codes that are displayed on the screen in operating system MS Windows of any version. The character table of the ASCII standard contains 255 codes by which all characters from 1 to 255 are numbered. For example, the capital (uppercase) letter A has code 97. The number 9 according to the same table is assigned code 57. The code for the space character is 32, and the line break code has the number 10. The same table contains non-printing characters from the keyboard.

Knowing the corresponding codes from the ASCII table and using the CHARACTER function, we are able to insert any character into Excel formula. In the example described above, a line break character is inserted, which is connected on both sides by the & operator between the values of cells A2 and C2.

If the “Wrap text” mode is not enabled in the cell, then the presence of the line break character will be invisible:

But on histograms, graphs and charts, line breaks inserted using formulas or entered from the keyboard (ALT+Enter) will be displayed naturally. That is, by hyphenation, breaking one text line into two or more.

This is a very common task and it can be solved very simply - to move text to a new line inside one Excel cell, you need to click ALT+ENTER(hold down the ALT key, then without releasing it, press the ENTER key)

How to Wrap Text on a New Line in Excel Using a Formula

In the window that opens, in the “Alignment” tab, you must check the box next to “Word wrap” as indicated in the picture, otherwise line wraps in Excel will not be displayed correctly using formulas.

How to replace a hyphen in Excel with another character and back using a formula

Can change the hyphen symbol to any other character, for example on a space, using the text function SUBSTITUTE in Excel

Let's take an example of what is in the picture above. So, in cell B1 we write the SUBSTITUTE function:

Code:

SUBSTITUTE(A1,CHAR(10);" ")

A1 is our text with a line break;
CHAR(10) is a line break (we looked at this a little higher in this article);
" " is a space because we are changing the line break to a space

If you need to do the opposite operation - change the space to a hyphen (symbol), then the function will look like this:

Code:

SUBSTITUTE(A1;" ";CHAR(10))

Let me remind you that in order for line breaks to be reflected correctly, you must specify “Wrap across lines” in the cell properties, in the “Alignment” section.

How to change the hyphen to a space and back in Excel using SEARCH - REPLACE

If we need to change the line break to a space, then in the “Find” line we need to enter a line break, to do this, go to the “Find” field, then press the ALT key, without releasing it, type 010 on the keyboard - this is the line break code, it is not will be visible in this field.

After that, in the “Replace with” field, enter a space or any other character that you need to change to and click “Replace” or “Replace All”.

By the way, this is implemented more clearly in Word.

If you need to change the line break character to a space, then in the "Find" field you need to indicate a special "Line Break" code, which is denoted as ^l
In the “Replace with:” field, you just need to make a space and click on “Replace” or “Replace All”.

You can change not only line breaks, but also other special characters, to get their corresponding code, you need to click on the “More >>”, “Special” button and select the code you need. Let me remind you that this function is only available in Word; these symbols will not work in Excel.

Standard rules for typing text in Excel cells do not allow you to type text on multiple lines. And the usual pressing of the Enter key, familiar to Word programs, only leads to a transition to the next cell.

The placement of text in lines can be set both after typing and during typing. In the first case, the cell text will consist of one paragraph, and in the second - of several.

How to specify multiple lines of text without creating paragraphs?

In this case, the text in the cell will consist of one paragraph, and the number of its lines will depend on the width of the cell.
1 way:

In the “Alignment” group, click on the “Text Wrap” button (Fig. 3.19).

Method 2:

In the open worksheet window, select the cells you want (with or without text) and go to the “Home” tab.
In the “Alignment” group, click the “Format Cells” button (or use the keyboard shortcut Ctrl+Shift+F).
In the “Format Cells” window, on the “Alignment” tab, in the “Display” group, activate the “Word Wrap” option.
Close the window with the "OK" button.

Advice

Later, by adjusting the column width, achieve the required number of lines of text in the cell.

How to specify multiple lines of text when creating paragraphs?

In the second option, each line in a cell will be a separate paragraph, and the number of lines will be the same for any column width.

In the open worksheet window, type the first line of text in the desired cell.
At the end of a line, to move to the second line of text, use the keyboard shortcut Alt+Enter.
In the future, if necessary, use this keyboard shortcut to create further rows in the cell.