In this article, we will learn how to count the number of lines in Excel.
In Excel, while giving text in the cell, sometimes we use next line instead of new cell. And then counting the same values we don't get the actual results.
So to count the values, we will use a formula. Formula having LEN & SUBSTITUTE function
The LEN function in Excel counts characters in a string. I mainly use it when need to extract text from string.
String : text string value
SUBSTITUTE function finds and replaces the new text by old text in the text string.
Old_text is the find text
New_text is the replace text
[instance] is an optional argument, The instance to replace. If not provided all the instances will be replaced.
Now we will make a formula out of it.
Let’s understand this via testing it on some examples.
Here we have names of fruit as values. And we need to count the number of fruits in the database using Excel formulas.
Use the formula
LEN ( C2 ) will return 14.
LEN(SUBSTITUTE ( C2 ,CHAR (10) ,"" )) will return 13.
+1 to get the count of lines in a cell.
Here the arguments to the function is given as cell reference. Press Enter to get the results.
Here the function returns 2 as the number of fruits in C2 cell.
Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl + D.
Now we got the count of number of different values in the respective cells.
Now get the count of all values via summing up all the count values.
Use the formula:
The argument values to the function is given as cell reference.
Press Enter to get the total count.
As you can see the function returns 9, count of all the fruit names.
The function returns the count 1 for the blank cell, So to ignore blank cells use the below formula.
Use the formula:
The above formula catches the blank cell or it returns the outcome for all other values.
Hope you understood how to count the number of lines in Excel. Explore more text function article here. Please feel free to state your queries in the comment box. We will certainly help you.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.