Rafiul Hasan
You may have merged data of multiple cells into a single cell in Excel. But, sometimes your Excel worksheet may contain data of different categories in a single cell and you may not have permission to use multiple cells for the multiple data. What you can do here is insert the Carriage Return character in the working cell. In this article, I will show you how to insert carriage return in an Excel Cell.
Download Practice Workbook
You can download the practice book below.
What Is Carriage Return in Excel Cell?
Carriage Return is an action performed in Excel to push some of the texts of a cell in a workbook to a new line of the same cell. Sometimes we combine the data of adjacent cells to a new cell, and at that time, carriage return to the new cell is performed. Carriage return helps to add a line break to a single cell instead of using multiple cells. It is used to make the data more reader-friendly as it separates the data into new lines and makes comfort for the eyes.
3 Ways to Insert Carriage Return in an Excel Cell
In this section, you will find 3 simple and effective ways to insert carriage return in an Excel cell. Let’s check them now!
1. Using Keyboard Shortcut
Let’s say, a cell is describing the name of a student studying a specific subject at a University.
You will notice that the data described here are separated by commas. Three data in a single is disturbing to the eyes actually. We don’t want to use 3 different cells for these data but at the same time, we want to make it eye soothing. So, we want to insert carriage return to this cell to create new lines for these 3 data. In order to do so, proceed with the following steps.
Steps:
- First of all, take the cursor after the comma that separated the first text data.
- Then, press ALT+ENTER and you will see that a new line has been created.
- Now, double-click on the cell and take the cursor after the next comma and again press ALT+ENTER.
- Hence, you will find 3 new lines for your dataset.
So easy, isn’t it? You can insert carriage return in the blink of an eye in this way!
Read More: How to Replace Carriage Return with Comma in Excel (3 Ways)
2. Insert Carriage Return Using Formula
Let’s say, we have got a dataset of the name of some students studying in different Universities and their relevant departments.
We want to combine the data for each student in just a single cell and insert carriage return by applying formula. Here, I will demonstrate 3 formulas for inserting carriage return.
2.1. Applying CHAR Function
Here, I will use the CHAR function to create a line break. Follow the steps below for this.
Steps:
- Firstly, create a column and apply the following formula to the selected cell.
=B5&" "&CHAR(10)&C5&" "&CHAR(10)&D5
Here,
- B5= Name of the Student
- C5= Department
- D5= University
Formula Breakdown
- B5 & “ ”= the cell value of B5 and “ ” denotes a space after the value.
- CHAR(10)= a line break
So, B5&” “&CHAR(10) returns Mike.
And B5&” “&CHAR(10)&C5&” “&CHAR(10) returns Mike Applied Physics.
Finally, B5&” “&CHAR(10)&C5&” “&CHAR(10)&D5 returns Mike Applied Physics Purdue University.
- Now, the cell will show new lines for each data (i.e. Name, Department, University).
- After that, use the Fill Handle tool to Autofill the formula down to the next cells.
2.2. Using CONCATENATE Function
For our same set of data, we will now use the CONCATENATE function to get the same result. In order to do so, just proceed with the steps below.
Steps:
- Firstly. Apply the following formula to the selected cell.
=CONCATENATE(B5,CHAR(10),C5,CHAR(10),D5)
Here,
- B5= Name of the Student
- C5= Department
- D5= University
- Then, use the Wrap Text option just like Method 2.1.
- Hence, you will see line breaks.
- Now, drag the formula for other cells to get the same result.
Read More: Carriage Return in Excel Formula to Concatenate (6 Examples)
2.3. Using TEXTJOIN Function
At this moment, we will show the use of the TEXTJOIN function. So, let’s start the process like the one below.
Steps:
- First of all, apply the following formula to the selected cell.
=TEXTJOIN(CHAR(10),TRUE,B5:D5)
Here,
- B5= Name of the Student
- C5= Department
- D5= University
- Then, follow the same procedure as Method 2.1 to get the output.
Similar Readings
- [Fixed!] Carriage Return Not Working in Excel (2 Solutions)
- How to Find Carriage Return in Excel (2 Easy Methods)
3. With Find and Replace Dialogue Box
Now, we have the same dataset as Method 2 but here, the data are just in a single cell separated by commas.
Here, we will use the Find and Replace dialogue box to insert a carriage return. For this, proceed with the steps below.
Steps:
- First, click CTRL+H to open the Find and Replace dialogue box. Or, you can also go to the Home tab> click Find & Select> Replace.
- Then, press CTRL+J on the replace with field and click Replace All.
- As a result, carriage return will be inserted.
Read More: How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)
Things to Remember
- Carriage return is to be inserted to create new lines to a single cell.
- CHAR(10) is used to insert the carriage return character.
Conclusion
In this article, I have tried to show you some methods of how to insert carriage return in an Excel cell. I hope this article has shed some light on your way to insert carriage return in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!
