Explanation

First, the LEN function counts total characters in the cell B5.

LEN returns the result inside of a second LEN, which counts characters without carriage returns.

The formula in the example shown will return 1 even if a cell is empty.

Excel formula: Add a line break with a formula

LEN will also count characters in numbers, but number formatting is not included.

SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching.

CHAR can insert characters that are hard to enter into a formula.

Excel formula: Count total words in a cell

For example, if A1 contains “apple”, ISBLANK(A1) returns FALSE.

How to concatenate with line breaks

Excel formula: Extract multiple lines from a cell

Excel LEN function

Excel SUBSTITUTE function

Excel CHAR function

Excel ISBLANK function

Article image