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),""))