How to move to a new line in Excel. How to wrap text in a cell in Excel. Using a keyboard shortcut

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

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

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

The picture below shows various symbols and their corresponding codes; several columns represent different fonts. To enlarge the image, click on the picture.

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

In Excel, there are several options for moving text within one cell.

1 way

You need to use the cell formatting tool.

1) Right-click on the desired cell or on several cells at once in which you want to wrap text. From the context menu, select Format Cells.

2) The formatting window opens. You need to open the “Alignment” tab and in the “Display” block check the “Word Wrap” box.

3) All that remains is to click on the “OK” button. The text will be wrapped and will appear not in one line, but in several.

Method 2

1) Select the desired cell; to do this, simply click on it with the left mouse button.

2) On the Excel toolbar, click on the “Text Wrap” button.

This method is much more convenient than the previous one and takes less time.

3 way

1) Go to text editing mode, to do this, double-click the left mouse button inside the cell. The cursor must be placed in front of the part of the text that needs to be moved.

2) Now type the key combination “Alt” + “Enter” on your keyboard. The text will be split.

3) To see the final result, just exit the cell editing mode.

To exit the editing mode, you can press the "Enter" key or click once with the left mouse button on any other table cell.

In Microsoft Excel, users encounter a wide variety of problems and situations. It happens that you have to work with a block of text while being in one area of the table. Unfortunately, this task causes certain difficulties for many users. In this article we will figure out how to wrap text in a cell in Excel. Let's get started. Go!

As you know, even if you are in the same cell, pressing the Enter key will move the cursor to the next row of the table, which is unacceptable in our case. This problem arises quite often and is easier to solve than most users think. To move text in one cell, use the Alt+Enter key combination. This method is good if you need to perform the operation once or several times.

You can do the same using Excel functions. Using functions makes it much easier to perform most tasks in the program. For example, enter contact information (telephone, email, home address) in separate cells so that they are automatically collected line by line in one cell.

First concatenate the text areas (“phone:” + “12345678”; “email:” + “ [email protected]"; “Address:” + “street A, building. 27-G"). This is done by adding the “&” (ampersand) sign between the cell addresses.

Now you need to create a formula that should look like this: “=B1&C1&CHAR(10)&B2&C2&CHAR(10)&B3&C3”.

CHAR(10) is a line break that is added after each pair of related areas.

If the transferred text is not displayed correctly, right-click on the desired area and select “Format Cells” in the menu that appears. In the window that opens, go to the “Alignment” tab. In the “Display” section, check the box next to “Word wrapping”.

If you need to move the words written in a column into a cell so that they are in one line separated by a space, write the following formula in the special field for functions: “=SUBSTITUTE(B2);CHARACTER(10);” "".

To perform the operation in the opposite direction, swap "CHAR(10)" and space (" ") in the formula.

There is a way that allows you to avoid using formulas. First you need to select the text and press the key combination Ctrl+H. In the window that appears, in the “Find” field, enter 010 while holding down the Alt key. Please note that the line break will not appear in the code you entered. Next, in the “Replace with” area, enter the character with which you would like to replace the line break character, in our case it is a space (" "). You can find all kinds of special characters and symbols that are not on the keyboard in a special Excel table.

For those who use VBA (Visual Basic) in Excel, the following method will also be relevant. Having marked the required areas, call the macro window. Then, depending on what you need to do, write one or the other commands, as shown in the screenshot below:

For convenience, copying the command is the same in the text below:

1. Change spaces to hyphens in selected cells using VBS:
Sub SpacesToHyphens()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(32), Chr(10))
2. Change hyphens to spaces in the selected cells using VBS:
Sub WrapsToSpaces()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(10), Chr(32))

Where Chr(10) and Chr(32) are line break and space break codes, respectively.

Each of the considered methods is good and convenient in its own way. Use the one that is most suitable for solving the current problem. As you can see, this problem can be solved quite simply. Use Microsoft Excel features to automate some of the program's operations to save time and improve productivity and efficiency. Write in the comments whether the article helped you resolve your questions and continue to learn something new about the enormous capabilities of the Microsoft Excel table editor.

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

First, we need to concatenate the text in cells A1 and B1 (A1&B1), A2 and B2 (A2&B2), A3 and B3 (A3&B3)

After this, we will combine all these pairs, but we also need to put a line break character (code) between these pairs. There is a special table of signs (the table is at the end of this article) that can be displayed in Excel using the special function SYMBOL(number), where number is a number from 1 to 255 that defines a specific sign.
For example, if we enter =SYMBOL(169), we will get the copyright symbol ©

We need a line break character, it corresponds to the serial number 10 - we need to remember this.

Line break code (character) - 10 Therefore, moving a line in Excel as a function will look like this SYMBOL(10)

Note: In VBA Excel, line breaks are entered using the Chr function and look like Chr(10)

So, in cell A6 we write the formula

A1&B1&CHAR(10)&A2&B2&CHAR(10)&A3&B3

As a result, we should get the result we need.
Note! In order for line breaks to be displayed correctly, you must enable “line wrap” in the cell properties.
To do this, select the cell(s) you need, right-click and select “Format Cells…”

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:

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:

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

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.

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 various symbols and their corresponding codes, several columns are different fonts. To enlarge the image, click on the picture.

If you periodically create documents in Microsoft Excel, then you have noticed that all data entered into a cell is written on one line. Since this may not always be suitable, and the option to stretch the cell is also not appropriate, the need arises to wrap the text. The usual “Enter” press is not suitable, since the cursor immediately jumps to a new line, so what should I do next?

In this article, we will learn how to move text in Excel to a new line within one cell. Let's look at how this can be done in various ways.

Method 1

You can use the key combination “Alt+Enter” for this. Place italics in front of the word that should start on a new line, press “Alt”, and without releasing it, click “Enter”. Everything, italics or phrase will jump to a new line. Type all the text in this way, and then press “Enter”.

The bottom cell will be selected, and the one we need will increase in height and the text in it will be fully visible.

To perform some actions faster, check out the list of shortcut keys in Excel.

Method 2

To ensure that while typing words, italics automatically jump to another line when the text no longer fits in width, do the following. Select a cell and right-click on it. In the context menu, click Format Cells.

At the top, select the “Alignment” tab and check the box next to “word wrap”. Click "OK".

Write everything you need, and if the next word does not fit in width, it will start on the next line.

If in a document lines must be wrapped in many cells, then first select them, and then check the box mentioned above.

Method 3

In some cases, everything that I described above may not be suitable, since it is necessary that information from several cells be collected in one, and already divided into lines in it. So let's figure out what formulas to use to get the desired result.

One of them is SYMBOL(). Here, in brackets, you need to indicate a value from one to 255. The number is taken from a special table, which indicates which character it corresponds to. To move a line, code 10 is used.

Now about how to work with the formula. For example, let's take data from cells A1:D2 and write what is written in different columns (A, B, C, D) in separate lines.

I put italics in the new cell and write in the formula bar:

A1&A2&CHAR(10)&B1&B2&CHAR(10)&C1&C2&CHAR(10)&D1&D2

We use the “&” sign to concatenate cells A1:A2 and so on. Press "Enter".

Don't be afraid of the result - everything will be written in one line. To fix this, open the “Format Cells” window and check the transfer box, as described above.

As a result, we will get what we wanted. The information will be taken from the indicated cells, and where CHAR(10) was entered in the formula, a transfer will be made.

Method 4

To move text in a cell, another formula is used - CONCATENATE(). Let's take only the first line with the headings: Last Name, Debt, Payable, Amount. Click on an empty cell and enter the formula:

CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1,CHAR(10),D1)

Instead of A1, B1, C1, D1, indicate the ones you need. Moreover, their number can be reduced or increased.

The result we will get is this.

Therefore, open the already familiar Format Cells window and mark the transfer item. Now the necessary words will begin on new lines.

In the next cell I entered the same formula, only I indicated other cells: A2:D2.

The advantage of using this method, like the previous one, is that when the data in the source cells changes, the values in these will also change.

In the example, the debt number has changed. If you also automatically calculate the amount in Excel, then you won’t have to change anything else manually.

Method 5

If you already have a document in which a lot is written in one cell, and you need to move words, then we will use the SUBSTITUTE() formula.

The gist of it is that we will replace all spaces with a line break character. Select a blank cell and add the formula to it:

SUBSTITUTE(A11;" ";CHAR(10))

Instead of A11 there will be your original text. Press the “Enter” button and immediately each word will be displayed on a new line.

By the way, in order not to constantly open the Format Cells window, you can use the special “Move Text” button, which is located on the “Home” tab.

I think the methods described are enough to move italics to a new line in an Excel cell. Choose the one that is most suitable for solving the task.

As you know, by default, one cell of an Excel sheet contains one row with numbers, text or other data. But what if you need to move text within one cell to another line? This task can be accomplished using some of the program's features. Let's figure out how to make a line feed in a cell in Excel.

Text wrapping methods

Some users try to move text inside a cell by pressing the Enter button on the keyboard. But by doing this they only achieve that the cursor moves to the next line of the sheet. We will consider transfer options within the cell, both very simple and more complex.

Method 1: Using the Keyboard

The simplest option for moving to another line is to place the cursor in front of the segment that needs to be moved, and then type the Alt + Enter key combination on the keyboard.

Unlike using just the Enter button, this method will achieve exactly the desired result.

Lesson: Hotkeys in Excel

Method 2: Formatting

If the user is not tasked with moving strictly defined words to a new line, but only needs to fit them within one cell without going beyond its boundaries, then you can use the formatting tool.

Select the cell in which the text extends beyond the boundaries. Click on it with the right mouse button. In the list that opens, select “Format Cells...”.
A formatting window opens. Go to the “Alignment” tab. In the “Display” settings block, select the “Word wrap” option by checking it. Click on the “OK” button.

After this, if the data extends beyond the boundaries of the cell, it will automatically expand in height and the words will begin to wrap. Sometimes you have to expand the boundaries manually.

To avoid formatting each individual element in this way, you can select an entire area at once. The disadvantage of this option is that the transfer is performed only if the words do not fit within the boundaries, and the division is carried out automatically without taking into account the user’s wishes.

Method 3: Using a Formula

You can also carry out transfer within a cell using formulas. This option is especially relevant if the content is output using functions, but it can be used in ordinary cases.

Format the cell as indicated in the previous option.
Select a cell and enter the following expression into it or into the formula bar:
CONCATENATE("TEXT1";CHAR(10);"TEXT2")

Instead of the elements “TEXT1” and “TEXT2” you need to substitute the words or sets of words that you want to transfer. The remaining symbols of the formula do not need to be changed.
To display the result on the worksheet, press the Enter button on your keyboard.

The main disadvantage of this method is the fact that it is more difficult to implement than previous options.

Lesson: Useful Excel Features

In general, the user must decide for himself which of the proposed methods is best to use in a particular case. If you only want all the characters to fit within the boundaries of the cell, then simply format it as necessary, or it is best to format the entire range. If you want to transfer specific words, then type the appropriate key combination, as described in the description of the first method. The third option is recommended to be used only when data is pulled from other ranges using a formula. In other cases, the use of this method is irrational, since there are much simpler options for solving the problem.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Did this article help you?

Quite often the question arises, how to move to another line inside a cell in Excel? This question arises when the text in a cell is too long, or when wrapping is necessary to structure the data. In this case, it may not be convenient to work with tables. Typically, text is transferred using the Enter key. For example, in Microsoft Office Word. But in Microsoft Office Excel, when we press Enter, we go to the adjacent lower cell.

So we need to wrap the text to another line. To transfer, you need to press the keyboard shortcut Alt+Enter. After which the word located on the right side of the cursor will be moved to the next line.

Automatically wrap text in Excel

In Excel, on the Home tab, in the Alignment group, there is a “Text Wrap” button. If you select a cell and click this button, the text in the cell will wrap to a new line automatically depending on the width of the cell. Automatic transfer requires a simple click of a button.

Remove hyphen using function and hyphen symbol

In order to remove the carry, we can use the SUBSTITUTE function.

The function replaces one text with another in the specified cell. In our case, we will replace the space character with a hyphen character.

Formula syntax:

SUBSTITUTE (text; old_text; new_text;)

The final form of the formula:

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

A1 – cell containing hyphenated text,

CHAR(10) – line break character,

» » – space.

If, on the contrary, we need to insert a hyphen to another line, instead of a space, we will perform this operation in reverse.

SUBSTITUTE(A1; ";CHAR(10))

For the function to work correctly, the “Wrap by words” checkbox must be checked in the Alignment tab (Cell Format).

Transfer using the CONCATENATE formula

To solve our problem, we can use the CONCATENATE formula.

Formula syntax:

CONCATENATE (text1,...)

We have text in cells A1 and B1. Let's enter the following formula into B3:

CONCATENATE(A1,CHAR(10),B1)

As in the example I gave above, for the function to work correctly, you need to set the “word wrap” checkbox in the properties.