My spreadsheet looks like this:
Is there a way with a formula to get in the B row the number of lines (including line breaks) as you see in the picture below?
Add a comment
|
2 Answers
Given your example setup:
=ArrayFormula(IF(A:A="",,LEN(A:A)-LEN(SUBSTITUTE(A:A,CHAR(10),""))+1))
To be clear, this counts number of lines, not number of line breaks. One line has zero line breaks. Two lines has one line break, etc.
This formula starts with the full length of each cell's contents (including line breaks) and then subtracts the length of each cell without those line breaks. That gives the total number of line breaks present, to which we add 1 to get the total number of lines.
answered Mar 26, 2021 at 20:00
-
Thanks for the answer, but the formula doesn't work. it says #ERROR– HASECommented Mar 27, 2021 at 10:59
-
1You may be in a locale that uses semicolons where we use commas in the United States. Try changing every comma (4) in the formula to a semicolon and see if that works. If not, please share a link to a sample spreadsheet, being sure to set the link's Share permission (when you create the link) to "Anyone with the link..." and "Editor." Commented Mar 27, 2021 at 15:36
-
It worked by changing the commas with semicolon. Tank you Erik for your great help– HASECommented Mar 27, 2021 at 16:51
-
You're welcome. Glad it was an easy fix. If you would, please take a moment to mark the answer as "Best Answer." This helps other contributors know at a glance which issues are fully resolved; and it helps future site visitors like yourself quickly find the solutions that might best match their needs. Thanks. Commented Mar 27, 2021 at 20:08
Thank you for this formula it's great!
Now, I also want to use the same scenario above, where a cell has multiple values within it, and I need to find the duplicates in a column that has multiple line breaks in each cell, is there a way to do this?
