What happens when you type something in a Microsoft Office Word document and press the ‘Enter’ key? The cursor would go on the next and you can start typing. Now write something in an Excel cell and just press ‘Enter’. Did it move to the next line in the same cell or to some other cell? The answer is – ‘Other Cell’. Then, what is the solution in excel, if you want to move to the next line in the same cell. The Insert Line Break feature in Excel would be helpful to achieve this. There are multiple ways that can be used to move to a new line in an Excel cell. In this blog, we would unlock all of these ways to insert a line break and move to the new line in Excel Cell.
Let us first make our sample data ready with us.
Sample Data
Refer to the screenshot below. The different excel tools in cell A1 are separated by comma and space characters.
You wish to have the result as below:
Different Methods to Add Line Break
As mentioned in the introduction paragraph of this blog, there are multiple methods to insert a line break in excel, both formula as well as non-formula based methods.
1. Using Keyboard Shortcut
2. Formula – ‘SUBSTITUTE’
3. Using Find and Replace Feature
Let us learn all the methods one by one.
Insert Line Break Using Keyboard Shortcut
This is the easiest and very user-friendly method to add a new line in an excel cell. It is most often used as this does not require to learn or form any complex formula.
The keyboard shortcut is Alt+Enter.
Follow the below steps:
Double-click on the cell B2 to open the cell in edit mode.
Then, put your cursor at the location in the cell from where you want to add a new line. In our case, I want to add a new line after each comma and space character.
Then just press Alt+Enter key combinations on your keyboard. You would notice that a new line gets added.
Similarly, perform the same activity with other words in the cell B2.
Refer to the demonstration below:
Finally, you would see that as desired, all the excel tools are in different lines in the same cell B2.
SUBSTITUTE Formula in Excel
Unlike the above method, this is a formula based approach to add a new line in the same cell in Excel.
The SUBSTITUTE formula replaces the text or part of the text with the desired one in a cell in excel.
Just enter the formula in the cell where you want the result:
=SUBSTITUTE(B2,”, “,CHAR(10))
The above formula contains three components. The first one is the cell in which you want to apply the substitution. The second attribute is the text that you want to replace (in our case it is the ‘comma followed by space’ i.e. “, “. And the last component, CHAR(10), is the replacement value/text.
CHAR(10) is nothing but an ASCII Code for inserting or adding a line break in a cell in Excel.
As soon as you enter the above formula in the destination cell and press Enter, you would notice that the excel would remove the comma and space characters between the words, but does not bring the words in different lines in a cell A5.
To achieve this, just select this cell A5 and click on the ‘Wrap Text’ option under the ‘Home’ tab (Group – Alignment).
The words are now in different lines in cell A5.
Insert Line Break Using Find and Replace Function
The ‘Find and Replace’ is also a non-formula based approach to insert a line break. It functions in a similar way as the SUBSTITUTE formula works.
Use the below steps to achieve the same:
Select the cell A2 and press Ctrl+H on your keyboard to open the ‘Find and Replace’ dialog box on your screen.
You can even open this dialog box using the path: ‘Home’ Tab > ‘Editing’ Group > ‘Find & Search’ button > ‘Replace’ option.
In the ‘Find What’ section of this dialog box, type comma followed by a space character (, ).
In the ‘Replace With’ section, simply press Ctrl+J (It represents line break).
As soon as you click on the ‘Replace All’ button, you would notice that the Excel inserts the line break between the words which were previously separated by a comma and space. Every excel tool is now in a different line but in the same cell.
Refer to the screenshot below:
This brings us to the end of this blog.
To learn how to extract values in multiple lines, click on the link – Extract Values in Multiple Lines in Cell in Excel
Share your views and comments in the comment section below and help us to improve our blogs.