Guria
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.
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")
- After this, press Enter.
- Here, you can see the existence of the Carriage Return stated as Yes.
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.
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)
- After this, hit Enter.
- Following, you will see the carriage return position is given 37.
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.
- Then, choose Replace from its drop-down menu.
- Following, the Find and Replace window will appear.
- Here, insert Ctrl + J in the Find what box.
- Next, insert a Comma (,) under the Replace with box.
- Lastly, click on the option Replace All.
- Furthermore, it will show you the number of changes made in a new dialogue box.
- 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)," ")
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.
- 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.
