In Google Spreadsheet, if you want to count the sum of the number of lines in all cells in column A, you can use this function:
=counta(A1:A) + sum(arrayformula(len(regexreplace(A1:A, "[^\n]", ""))))
What’s the equivalent in OnlyOffice? I tried the same function, and it doesn’t work and shows the result as #NAME?
Hi @IanY
RegEx is not a built-in feature in the OO Spreadsheet editor.
I can’t recall an equivalent feature off the top of my head, and I’ll verify and get back to you with an answer.
Hi @IanY
It is an interesting problem, but the best I could find or derive was this.
The formula in cell B3 was;
=LEN(B1)-LEN(CLEAN(B1))+1
But it will only work on a single cell.
Any attempt at a range of cells, as far as I could determine, would require a string concatenation function which strips away the carriage returns from each cell, thus defeating the process.
Maybe a macro could parse a range of cells doing some operation, but macros are out of my knowledge scope.
Otherwise, the aforementioned formula would need to duplicated for each cell in the range, and then accumulated.
Probably not the most idea methodology…
Also, you can try the following formula: =COUNTA(A:A)+LEN(TEXTJOIN("",TRUE,A:A))-LEN(SUBSTITUTE(TEXTJOIN("",TRUE,A:A),CHAR(10),""))
I’ve attached the file.
count_rows.xlsx (8.0 KB)
