A line break is a command to add space between lines of text in a cell. It is a special character for CHAR(10) in Excel. To figure out the count of cells with line breaks, we will use the FIND function and the SUMPRODUCT function.
The FIND function returns the position of a text string, and the SUMPRODUCT function returns the product of numbers in an array.
Example: In the following database, the city in column A is the raw data that contains the line breaks, and the clean data is in column B.
Formula: To count the number of cells that include line breaks in column A
=SUMPRODUCT(–(ISNUMBER(FIND(CHAR(10), A2:A12))))
=SUMPRODUCT((ISNUMBER(FIND(CHAR(10), A2:A12)))*1)
- FIND(CHAR(10), A2:A12)) is to find the line break in the cells.
- ISNUMBER(FIND(CHAR(10), A2:A12))) returns true when the cell contains the line break, and false if does not.
- The double hyphens symbol (or times 1) is to convert true into 1 and false into 0.
- The SUMPRODUCT function returns the sum of the products of the new array.
In the example, there are three cells with line breaks.
Notes: The SUMPRODUCT Function
The SUMPRODUCT function adds all the multiplication results for all arrays.
Formula:
=SUMPRODUCT(array1, [array2], …)
Explanations:
– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.