In this lesson, we will explore such useful functions of Microsoft Excel as line wrapping and combining multiple cells into one. With these functions, you can wrap text across multiple lines, create headers for tables, write long text on one line without increasing the width of columns, and much more.
Very often the content cannot be fully displayed in the cell, because its width is not enough. In such cases, you can choose one of two options: transfer the text along lines or combine several cells into one, while you do not need to change the width of the columns.
As text wraps, the line height will automatically adjust to allow the content to appear on multiple lines. Merging cells allows you to create one large cell by merging several adjacent ones.
In the following example, we will apply line wrapping to column D.
Press command Transfer text again to undo the transfer.
When two or more cells are merged, the resulting cell takes the place of the merged cells, but the data is not added. You can combine any contiguous range, and even all cells on the sheet, and the information in all cells except the upper left will be deleted.
In the example below, we will concatenate the range A1: E1 to create a title for our sheet.
The button acts like a switch, i.e. clicking it again will cancel the merge. Deleted data will not be restored.
More options for merging cells in Excel
To access additional options for merging cells, click the arrow next to the command icon Merge and center... A drop-down menu will appear with the following commands:
This instruction will introduce you three ways to remove carriage returns from cells in Excel. You will also learn how to replace line breaks with other characters. All suggested solutions work in Excel 2013, 2010, 2007 and 2003.
Line breaks can appear in text for various reasons. Usually carriage returns are found in a workbook, for example when text is copied from a web page, when they are already in a workbook received from a client, or when we add them ourselves with key presses Alt + Enter.
Whatever the reason for their appearance, we are now faced with the task of removing carriage returns, as they interfere with the search for phrases and lead to clutter in the column when the text wrapping mode is enabled.
All three methods presented are pretty fast. Choose the one that suits you best:
Comment: Originally, the terms “carriage return” and “line feed” were used when working on typewriters and meant two different operations. An inquisitive reader can.
Computers and word processing software have been developed with the characteristics of typewriters in mind. This is why two different non-printable characters are now used to denote a line break: Carriage return (Carriage return, CR or ASCII code 13) and Line translation (Line feed, LF or ASCII code 10). Windows uses both characters together, while * NIX systems use only line feeds.
Be careful: Both options appear in Excel. When importing from files .txt or .csv the data usually contains carriage returns and line feeds. When a line break is entered manually by pressing Alt + Enter, Excel only inserts a line feed character. If the file .csv received from a fan of Linux, Unix or other similar system, then prepare to meet only the line feed character.
Removing carriage returns manually
Pros: This method is the fastest.
Minuses: No extra goodies
This is how you can remove line breaks using the " Find and replace»:
Remove line breaks using Excel formulas
Pros: You can use sequential or nested formulas for complex text validation in the processed cell. For example, you can remove carriage returns and then find extra leading or trailing spaces, or extra spaces between words.
In some cases, line breaks must be removed in order to later use the text as arguments to functions without changing the original cells. The result can be used, for example, as an argument to a function Browse (LOOKUP).
Minuses: You will need to create a helper column and follow many additional steps.
Remove line breaks with VBA macros
Pros: Create once - use over and over again with any workbook.
Minuses: At least basic knowledge of VBA is required.
The VBA macro from the following example removes carriage returns from all cells on the active sheet.
Sub RemoveCarriageReturns () Dim MyRange As Range Application.ScreenUpdating \u003d False Application.Calculation \u003d xlCalculationManual For Each MyRange In ActiveSheet.UsedRange If 0< InStr(MyRange, Chr(10)) Then MyRange = Replace(MyRange, Chr(10), "") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub
If you are not too familiar with VBA, I recommend that you study the article on
If you periodically create documents in Microsoft Excel, then you noticed that all the data that is entered into a cell is written in one line. Since this may not always work, and the option to stretch the cell is also not appropriate, it becomes necessary to wrap the text. Habitual pressing "Enter" is not suitable, because the cursor immediately jumps to a new line, and what to do next?
In this article, we will learn how to transfer text in Excel to a new line within one cell. Let's see how this can be done in various ways.
Method 1
You can use the "Alt + Enter" key combination for this. Put italics in front of the word that should start on a new line, press "Alt", and without releasing it, click "Enter". Everything, italics or the 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 cell we need will increase in height and the text in it will be fully visible.
To get things done faster, check out the list of keyboard shortcuts in Excel.
Method 2
In order to automatically jump to another line while typing words when the text no longer fits in width, do the following. Select a cell and right-click on it. On the context menu, click Format Cells.
At the top, select the "Alignment" tab and set the checkbox opposite the item "Wrap by words"... Click OK.
Write everything you need, and if the next word does not fit in width, it will start on the next line.
If the lines in the document should wrap in many cells, then first select them, and then put a tick in the box mentioned above.
Method 3
In some cases, everything that I have described above may not work, since it is necessary that information from several cells be collected in one, and already divided into lines in it. Therefore, let's figure out which formulas to use to get the desired result.
One of them is SYMBOL (). Here in brackets you need to specify a value from one to 255. The number is taken from a special table, which indicates which character it corresponds to. Code 10 is used for line transfer.
Now about how to work with the formula. For example, let's take the data from cells A1: D2 and what is written in different columns (A, B, C, D) will be done in separate lines.
I put italics in a new cell and write in the formula bar:
A1 & A2 & SYMBOL (10) & B1 & B2 & SYMBOL (10) & C1 & C2 & SYMBOL (10) & D1 & D2
With the "&" we concatenate cells A1: A2 and so on. Press Enter.
Don't be alarmed by the result - everything will be written on one line. To fix this, open the "Format Cells" window and put a check mark in the transfer item, as described above.
As a result, we will get what we wanted. The information will be taken from the indicated cells, and where it was put in the SYMBOL formula (10), a transfer will be made.
Method 4
To wrap text in a cell, another formula is used -. 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; SYMBOL (10); B1; SYMBOL (10); C1; SYMBOL (10); D1)
Instead of A1, B1, C1, D1, specify the ones you need. Moreover, their number can be reduced or increased.
The result is as follows.
Therefore, open the already familiar Cell Format window and mark the drop point. Now the words you want will begin with new lines.
In the next cell, I entered the same formula, only indicated other cells: A2: D2.
The advantage of using this method, like the previous one, is that when the data in the original cells changes, the values \u200b\u200bin these will also change.
In the example, the debt count has changed. If you also automatically calculate the amount in Excel, then you will not have to manually change anything else.
Method 5
If you already have a document in which a lot is written in one cell, and you need to transfer words, then we will use the formula.
Its essence is that we will replace all spaces with a line break character. Select a blank cell and add the formula to it:
SUBSTITUTE (A11; ""; SYMBOL (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 button "Transfer text", which is located on the "Home" tab.
I think that the described methods will be enough to transfer italics to a new line in an Excel cell. Choose the one that best suits the task at hand.
If you periodically create documents in Microsoft Excel, then you noticed that all the data that is entered into a cell is written in one line. Since this may not always work, and the option to stretch the cell is also not appropriate, it becomes necessary to wrap the text. Habitual pressing "Enter" is not suitable, because the cursor immediately jumps to a new line, and what to do next?
In this article, we will learn how to transfer text in Excel to a new line within one cell. Let's see how this can be done in various ways.
Method 1
You can use the "Alt + Enter" key combination for this. Put italics in front of the word that should start on a new line, press "Alt", and without releasing it, click "Enter". Everything, italics or the 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 cell we need will increase in height and the text in it will be fully visible.
To get things done faster, check out the list of keyboard shortcuts in Excel.
Method 2
In order to automatically jump to another line while typing words when the text no longer fits in width, do the following. Select a cell and right-click on it. On the context menu, click Format Cells.
At the top, select the "Alignment" tab and set the checkbox opposite the item "Wrap by words"... Click OK.
Write everything you need, and if the next word does not fit in width, it will start on the next line.
If the lines in the document should wrap in many cells, then first select them, and then put a tick in the box mentioned above.
Method 3
In some cases, everything that I have described above may not work, since it is necessary that information from several cells be collected in one, and already divided into lines in it. Therefore, let's figure out which formulas to use to get the desired result.
One of them is SYMBOL (). Here in brackets you need to specify a value from one to 255. The number is taken from a special table, which indicates which character it corresponds to. Code 10 is used for line transfer.
Now about how to work with the formula. For example, let's take the data from cells A1: D2 and what is written in different columns (A, B, C, D) will be done in separate lines.
I put italics in a new cell and write in the formula bar:
A1 & A2 & SYMBOL (10) & B1 & B2 & SYMBOL (10) & C1 & C2 & SYMBOL (10) & D1 & D2
With the "&" we concatenate cells A1: A2 and so on. Press Enter.
Don't be alarmed by the result - everything will be written on one line. To fix this, open the "Format Cells" window and put a check mark in the transfer item, as described above.
As a result, we will get what we wanted. The information will be taken from the indicated cells, and where it was put in the SYMBOL formula (10), a transfer will be made.
Method 4
To wrap text in a cell, another formula is used -. 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; SYMBOL (10); B1; SYMBOL (10); C1; SYMBOL (10); D1)
Instead of A1, B1, C1, D1, specify the ones you need. Moreover, their number can be reduced or increased.
The result is as follows.
Therefore, open the already familiar Cell Format window and mark the drop point. Now the words you want will begin with new lines.
In the next cell, I entered the same formula, only indicated other cells: A2: D2.
The advantage of using this method, like the previous one, is that when the data in the original cells changes, the values \u200b\u200bin these will also change.
In the example, the debt count has changed. If you also automatically calculate the amount in Excel, then you will not have to manually change anything else.
Method 5
If you already have a document in which a lot is written in one cell, and you need to transfer words, then we will use the formula.
Its essence is that we will replace all spaces with a line break character. Select a blank cell and add the formula to it:
SUBSTITUTE (A11; ""; SYMBOL (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 button "Transfer text", which is located on the "Home" tab.
I think that the described methods will be enough to transfer italics to a new line in an Excel cell. Choose the one that best suits the task at hand.
In Microsoft Excel, users are faced with a wide variety of problems and situations. It happens that you have to work with a block of text while in the same area of \u200b\u200bthe 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 being in one cell, by pressing the Enter key, you 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 transfer text in one cell, use the Alt + Enter key combination. This method is good if you need to perform an operation once or several times.
You can do the same with Excel functions. The use of functions makes it possible to significantly facilitate most of the tasks in the program. For example, enter contact information (phone, email, home address) in separate cells so that they are automatically collected line by line in one cell.
First, concatenate areas of text ("phone:" + "12345678"; "email:" + " [email protected]"; "Address:" + "st. A, house. 27-D "). This is done by adding an "&" (ampersand) between the cell addresses.
Now you need to make a formula, which should look like this: "\u003d B1 & C1 & SYMBOL (10) & B2 & C2 & SYMBOL (10) & B3 & C3".
SYMBOL (10) is a line break that is added after each pair of related areas.
In the event that the transferred text is displayed incorrectly, right-click on the desired area and in the menu that appears, select "Format cells". In the window that opens, go to the "Alignment" tab. In the "Display" section, check the box next to "wrap by words".
If you need to move the words written in a column to a cell so that they stand in one line separated by a space, write the following formula in the special field for functions: "\u003d SUBSTITUTE (B2); SYMBOL (10);" "".
To reverse the operation, swap “SYMBOL (10)” and a space (”“) in the formula.
There is a way to avoid having to use 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 code you entered will not be displayed. Next, in the "Replace with" area, enter the character with which you would like to replace the line feed character, in our case it is a space (""). You can find all sorts of special characters and symbols that are missing from the keyboard in Excel's special table.
For those who use VBA (Visual Basic) in Excel, the following method will also be relevant. After marking 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 the convenience of copying the command, the same is with the text below:
1. Change spaces to hyphens in selected cells using VBS:
Sub Spaces On hyphens ()
For Each cell In Selection
cell.Value \u003d Replace (cell.Value, Chr (32), Chr (10))
2. Change the hyphenation to spaces on in the selected cells using VBS:
Sub hyphens on spaces ()
For Each cell In Selection
cell.Value \u003d Replace (cell.Value, Chr (10), Chr (32))
Where Chr (10) and Chr (32) are line feed and space codes, respectively.
Each of the considered methods is good and convenient in its own way. Use the one that is more appropriate for the current task. As you can see, this problem can be solved quite simply. Use the features of Microsoft Excel to automate some of the operations in your program to save time and improve productivity and efficiency. Write in the comments if the article helped you to sort out your questions and keep learning something new about the enormous possibilities of the Microsoft Excel spreadsheet editor.