Count number of line breaks in a cell
This tutorial shows how to count the number of line breaks in a cell using an Excel formula, with the LEN and SUBSTITUTE functions
|
=LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),""))+1
|
GENERIC FORMULA
=LEN(cell)-LEN(SUBSTITUTE(cell,CHAR(10),""))+1
ARGUMENTS EXPLANATION This formula uses the SUBSTITUTE function with char(10) to remove the line breaks from a cell and then applies the LEN function to count the number of character from the cell without line breaks. The LEN function is also used to calculate the number of characters from a cell which includes line breaks. The formula then subtracts the number of characters derived from a cell that excludes line breaks from number of characters derived from a string that includes line breaks.
|
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of spaces in a string | How to count the number of spaces in a string |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| LEN Function | The Excel LEN function returns the number of characters in a specified string |