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?


2 Answers 2


Given your example setup:


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.

  • Thanks for the answer, but the formula doesn't work. it says #ERROR
    – HASE
    Commented Mar 27, 2021 at 10:59
  • 1
    You 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."
    – Erik Tyler
    Commented Mar 27, 2021 at 15:36
  • It worked by changing the commas with semicolon. Tank you Erik for your great help
    – HASE
    Commented 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.
    – Erik Tyler
    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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.