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

Get FREE Advanced Excel Exercises with Solutions!

Carriage return is a very important element in excel cells. It defines the cell length along with the information within it. In some cases, we need to find the proper location of this. Therefore, in this article, we will learn how to find the carriage return in excel with 2 easy methods.


Download Workbook

Get the sample file to try it yourself.


What Is Carriage Return?

Theoretically, carriage return is the process of work to push some lines in a single cell and then move it within the cell. It makes the information more organized and neat. It also helps to define the consumption of spaces in a whole dataset.


Line Break vs. Carriage Return

Although the function of the line break and carriage return looks similar, there is a slight difference between them. A carriage return work to take the cursor at the beginning of the sentence and then separates it word by word, just like the old days’ typewriter.

On the other hand, a line break separates words and places them in another line.

A carriage return will lead to a line break when combined with Line Feed.


2 Easy Methods to Find Carriage Return in Excel

For illustration, here is a dataset of 3 person’s names and their addresses. As you can see the addresses are packed inside cells.

How to Find Carriage Return in Excel


Now, we will find the carriage return in these cells.

1. Search Carriage Return Using Excel IFERROR Function

In this first method, we will use the IFERROR function to search for carriage return. The IFERROR function is used to find and tackle errors in a formula. Let’s see the simple steps below:

  • First, insert a new column titled Carriage Return.
  • Then, apply this formula in cell D5.
=IFERROR(IF(FIND("

",C5)>0,"Yes","No"),"No")

Search Carriage Return Using Excel IFERROR Function

  • After this, press Enter.
  • Here, you can see the existence of the Carriage Return stated as Yes.

Search Carriage Return Using Excel IFERROR Function

Here, the IFERROR function is used to trap errors. Then, we used the IF function to determine the condition. Lastly, we applied the FIND function to search for the output whether it is Yes or No based on cell C5.

  • Following, use the AutoFill tool to drag the same formula in cells D6 and D7 to see the final result.

How to Find Carriage Return in Excel

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


2. Combine SEARCH & CHAR Functions to Find Carriage Return in Excel

In this second method, we will combine the SEARCH & CHAR functions to find carriage returns in the dataset. The functions will provide information about the position of the return character in a cell. Follow the process below:

  • Firstly, insert a new column named Carriage Return.
  • Secondly, put this formula in cell D5.
=SEARCH(CHAR(10),C5)

Combine SEARCH & CHAR Functions to Find Carriage Return in Excel

  • After this, hit Enter.
  • Following, you will see the carriage return position is given 37.

Combine SEARCH & CHAR Functions to Find Carriage Return in Excel

Here, we used the SEARCH function for finding the condition. Therefore, we gave the condition with the CHAR function where the value is 10 to define the position of the return character.

  • Lastly, apply a similar formula in cells D6 and D7 to get the final output.
  • As the information in cell C7 does not contain carriage return, therefore the result show #VALUE!

Read More: Carriage Return in Excel Formula to Concatenate (6 Examples)


How to Replace Carriage Return in Excel

At this stage, let us replace the character of carriage return with a comma in excel. For this, go through the steps below:

  • In the beginning, select cell range C5:C7.
  • Then, go to the Home tab and select Find & Replace under the Editing category.

How to Replace Carriage Return in Excel

  • Then, choose Replace from its drop-down menu.

How to Replace Carriage Return in Excel

  • Following, the Find and Replace window will appear.
  • Here, insert Ctrl + J in the Find what box.

How to Replace Carriage Return in Excel

  • Next, insert a Comma (,) under the Replace with box.

How to Replace Carriage Return in Excel

  • Lastly, click on the option Replace All.

  • Furthermore, it will show you the number of changes made in a new dialogue box.

How to Replace Carriage Return in Excel

  • Here, press OK.
  • Also, close the Find and Replace dialogue box
  • That’s it, we can see the final output.


How to Remove Carriage Return in Excel

In this last section, we will learn to remove carriage return in excel with these simple steps:

  • First, insert this formula in cell D5.
=SUBSTITUTE(C5,CHAR(10)," ")

How to Remove Carriage Return in Excel

Here, we used the SUBSTITUTE function to eliminate the existence of carriage return. Further, we applied the CHAR function with the value 10 to define its position.

  • Now, press Enter.
  • After this, turn off the Wrap Text option from the Home tab.

How to Remove Carriage Return in Excel

  • Finally, you can see the text in a single line like this:

  • Apply the similar procedure and you will get the final output.


Conclusion

I hope it was a helpful article for you on how to find the carriage return in excel. Let us know your feedback in the comment box. Follow ExcelDemy for more articles like this.


Related Articles

Sanjida Mehrun Guria

Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo