How to Insert Carriage Return in Excel Cell (3 Simple Ways)

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.

Insert Carriage Return in an Excel Cell

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.

Insert Carriage Return in an Excel Cell

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

Insert Carriage Return in an Excel Cell

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.

Insert Carriage Return in an Excel Cell

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.

  • Then, go to the Home tab and click Wrap Text.

Insert Carriage Return in an Excel Cell

  • Now, the cell will show new lines for each data (i.e. Name, Department, University).

Insert Carriage Return in an Excel Cell

  • After that, use the Fill Handle tool to Autofill the formula down to the next cells.

Insert Carriage Return in an Excel Cell

Read More: How to Find Carriage Return in Excel (2 Easy Methods)


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

Insert Carriage Return in an Excel Cell

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

Insert Carriage Return in an Excel Cell

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.

Insert Carriage Return in an Excel Cell

Read More: [Fixed!] Carriage Return Not Working in Excel (2 Solutions)


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.

Insert Carriage Return in an Excel Cell

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!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo