Excel Carriage Return
Excel has the advantage of wrapping the texts in the cell to make the adjustment of the data to fit in column alignment by enlarging the row. Wrapping the text allows the cells to increase their height but at the same time, it also decreases the width of the cell. Having said this when the text too large often times people also insert the carriage return character from an excel cell to push to the new line within the cell itself. So, in this article, we will show you how to insert carriage return in excel cell by using formula and if the carriage return is already inserted then we will show you how to remove them as well.
What is Carriage Return in Excel Cell?
Carriage Return in excel cell is the activity used to push some of the content of the cell to the new line within the same cell. When we are combining multiple cell data into one cell we may want to push some content to the next line to make it look neat and organized.
So, we can simply say “a line breaker or newline inserted within the same cell to push some of the contents of the next line”.
For an example look at the below data set.
We have three sentences in the cell A1 and each sentence is separated by a comma (,). What we need in this case is we need to push the second line to the next line and third line to the next line like the below one.
- As you can see above it has reduced the width of the row and increased the height of the row by placing a carriage return character.
- Ok, let’s get back to this and see how we can insert a carriage return in excel. First, our data looks like this in a cell.
- Place a cursor before the content which we need to push to the next line.
- Now press the excel shortcut key ALT + ENTER to insert the carriage return character in excel cell.
As you press ALT + ENTER key it has pushed the content in front of the selected data to the new line by inserting a carriage return in excel cell.
- Now again place a cursor in front of the third line data.
- Now again press the shortcut key ALT + ENTER.
It has inserted the carriage return character to push the data to the new line within the same cell.
Insert Excel Carriage Return by using Formula
In the case of concatenating values of different cells, we may need to push a couple of data to the next line.
- For example, look at the below data.
- From the above data, we need to combine these cell values but we need each cell value to be in a new line like the below.
Since we are dealing with more than one cell it is a tough task to keep pressing the shortcut key all the time instead we can use character function “CHAR” to insert a carriage return in excel.
- Apply the below formula as shown below.
- After applying the formula we could see the flat row, one small adjustment we need to do is to wrap the formula applied cells.
In this example, we have used the “CHAR(10)” function which inserts the “Carriage Return” character wherever we have applied.
How to Remove Carriage Return Character from Excel Cell?
If inserting the carriage return is the one skill then removing those carriage return characters is another set of skills we need to learn.
- Assume below is the data we have got from the web.
We can remove carriage return character from excel cell by using a couple of methods, the first method is by using the replace method.
- Select the data range and press the Ctrl + H shortcut key.
- We need to first find what the value we are replacing is, and in this case, we are finding the “Carriage Return” character to insert this character we need to press Ctrl + J.
We just need to remove carriage return character so leave “Replace with” part of this “Find and Replace” method.
- Now press the button “Replace All” and it will remove all carriage return characters.
- Another way of removing carriage return character is by using formula. Below is the formula to remove the carriage return character from the excel cell.
Things to Remember
- Excel Carriage Return cell is adding a new line within the cell.
- CHAR (10) can insert a new line.
- A combination of SUBSTITUTE and CHAR functions can remove carriage return characters.
This has been a guide to Carriage Return in Excel. Here we learn how to insert and remove carriage return character from excel cell by using formula along with examples and downloadable excel template. You may learn more about excel from the following articles –