Go Back

Count line breaks in cell

Sometimes, we use line breaks to break the lengthy content while using excel worksheet. By doing so, the content can be displayed completely in the cell. Have you ever thought of counting line breaks in the cell? In this article, we will show how to count line breaks in cell

Formula

You can use the formula given below to count line breaks in a cell. The formula is the combination of CHAR, SUBSTITUTE, and LEN functions.  

=LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),""))+1

Explanation

To start with, LEN function measures all of the characters present in B5. Subsequently, SUBSTITUTE expels all of the “line returns” from the content in B5 by searching “CHAR(10)” or, in other words, codes for returning character in the Windows. LEN restores the outcome within a second LEN and it calculates characters with no carriage returns. Subtraction occurs between second and the first count and 1 adds up in the final outcome.

Example

In this example, we count line breaks in a cell as shown in the screenshot. We have selected a cell next to a string that we desire to count line break. We typed the above-mentioned formula and press “Enter” to get the result. See screenshot

Figure 1 Example of Count Line Breaks in Cell

Notes

Of you are working on Mac system then use the following formula:

=LEN(SUBSTITUTE(A2,CHAR(13),""))

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi, I'm trying to split contents with line breaks in 1 cell into multiple cells.
Solved by A. Y. in 22 mins
I have some cells that have unwanted line breaks. I concatenated data together into one cell but there were some rows that were blank that caused the line breaks. I either need to fix the formula to skip blank cells on concatenation or I need to remove the line breaks in the combined cell. Help! On tight deadline and my data files are failing on upload due to this.
Solved by O. A. in 21 mins
I was just on with someone and my session ended. didn't have the option to add more credits. Can I get back on with the consultant? This was my question... I have some cells that have unwanted line breaks. I concatenated data together into one cell but there were some rows that were blank that caused the line breaks. I either need to fix the formula to skip blank cells on concatenation or I need to remove the line breaks in the combined cell. Help! On tight deadline and my data files are failing on upload due to this.
Solved by F. C. in 16 mins

Leave a Comment

avatar