Excel Formulas to Count Line Breaks in A Cell ~ A Complete Guide!!

Have you ever tried to count the number of line breaks in a certain cell? How can you calculate them in Excel Easily? Today, I will introduce some quick tricks to achieve them. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

You can use the below formula to count total lines in a cell.

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),””))+1

Syntax Explanations:

LEN – In Excel, the LEN function is used to find the length of the text string.

SUBSTITUTE – This function helps to replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE function.

CHAR – It helps to get a character by a specified number. Read more on the CHAR function.

A1 – It represents the input value.

Comma symbol (,) – It is a separator that helps to separate a list of values.

Parenthesis () – The main purpose of this symbol is to group the elements.

Plus operator (+) – This symbol is used to add the values.

Minus Operator (-) – This symbol will help to subtract any two values.

Practical Example:

Let’s consider the below example image.

Here we will enter the input values in Column B.

Now we need to count the total lines in a cell.