I need to count text rows in one cell.
Like at this screenshot http://joxi.ru/brRly91hQXZnB2
In one cell, I got 3 rows and I need make quick count for other cells.
Formula is easy =250*(quantity of rows in one cell). That’s how I see it.
Please, somebody help me!
Hi
If it is row (created by pressing Ctrl+Enter) you can use this:
=LEN(A1)-LEN(SUBSTITUTE(A1;CHAR(10);""))+1
Regards
Concerning the terms: Your cells do not contain tables, and thus no rows, but lines.
Assumed a (not to high) maximum number, say 100, of lines in any cell that should be tested, you may use the followig formula to get the number of lines in cell A1 (blank cells will return 1 as if they contain 1 empty line):
=SUMPRODUCT(MID(A1;ROW(OFFSET(INDIRECT("a1");0;0;100;1));1)=CHAR(10))+1
You may simplify the formula to
=SUMPRODUCT(MID(A1;ROW($A$1:$A$100);1)=CHAR(10))+1
but this may lead to #REF! errors after deletion of columns/rows and to wrong results after the insertion of rows above. Additional errors reserved. (Explanation if requested!)
A more general and more efficient solution may require user programming (in BASIC, e.g.).
Addendum: To design spreadsheets allowing for more than one line within a cell should be considered bad practise. The only excepition is, IMO, the analysis of imported texts containing the LF characters in advance.