Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles TEXT Count line breaks in cell

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