ThisCalculator

Everyday

Count Line Breaks in Cell: Excel & Google Sheets

Updated · Standard formula, shown below

This article explains how to count line breaks (newlines) inside a single cell in Excel and Google Sheets. You’ll learn the exact formulas to use, how they work, and common pitfalls to avoid. The answer is straightforward: use LEN(A1) - LEN(SUBSTITUTE(A1, CHAR(10), "")) in Excel, and the same formula works in Google Sheets, with CHAR(10) representing the line break character.

Why Count Line Breaks in a Cell?

Line breaks inside a cell are common when you import data from a text file, paste multi-line content, or use formulas like CHAR(10) to join values. Knowing how many line breaks exist helps you:

  • Verify that imported data was split correctly.
  • Count the number of lines or paragraphs in a cell (line breaks + 1).
  • Clean up data by removing or replacing line breaks.
  • Prepare data for export to CSV or other formats where line breaks might cause issues.

How the Formula Works

The core formula uses three functions: LEN, SUBSTITUTE, and CHAR.

  • LEN(text) returns the number of characters in a text string.
  • SUBSTITUTE(text, old_text, new_text) replaces all occurrences of a specific character or substring.
  • CHAR(10) is the line break character in Windows Excel and Google Sheets (on Mac, Excel uses CHAR(13) for legacy reasons, but CHAR(10) also works in most modern versions).

The logic is: count the total characters, then subtract the count of characters after removing all line breaks. The difference is exactly the number of line breaks.

Step-by-Step Instructions for Excel

  1. Click on the cell where you want the result (e.g., B1).
  2. Type the formula: =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))
  3. Press Enter. The result is the count of line breaks in cell A1.
  4. If the cell contains no line breaks, the result is 0.
  5. To count the number of lines (including the last line), add 1: =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1

Note: In Excel for Mac, you might need to use CHAR(13) instead of CHAR(10) if the line breaks were created with the old Macintosh line ending. However, most modern Excel for Mac uses CHAR(10) as well.

Step-by-Step Instructions for Google Sheets

  1. Select an empty cell (e.g., B1).
  2. Enter the same formula: =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))
  3. Press Enter. The count appears.
  4. For the number of lines, add 1.

Google Sheets uses CHAR(10) for line breaks, regardless of operating system. The formula works identically.

Common Mistakes and Misconceptions

One frequent mistake is using CHAR(13) instead of CHAR(10). In Windows, a line break in a cell is typically CHAR(10) (LF) only, not CRLF. If you copy text from a file, you might have both CHAR(13) and CHAR(10). In that case, count both:

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(10),""))

Another mistake is forgetting that LEN counts spaces as characters. If your cell has trailing spaces, they are counted, but that doesn’t affect the line break count because you’re subtracting the same total.

Also, note that line breaks are not visible in the formula bar; they appear as small squares or gaps in the cell. To see them, you can use the formula =CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) as an array formula, but that’s overkill for most tasks.

Alternatives and Advanced Techniques

If you need to count line breaks in multiple cells, you can use the same formula with relative references and drag down. For a range, you can use SUMPRODUCT:

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,CHAR(10),"")))

This gives the total line breaks in the range A1:A10. To count lines, add COUNTA(A1:A10) to account for the last line in each cell.

In Google Sheets, you can also use SPLIT to count lines: =COUNTA(SPLIT(A1,CHAR(10))) returns the number of lines, but it fails if the cell is empty. The LEN method is more robust.

Frequently Asked Questions

Why does my formula return 0 even though I see line breaks?

This usually happens when the line breaks are not the standard CHAR(10). For example, if the cell contains a line break from a Mac file, it might be CHAR(13). Try replacing CHAR(10) with CHAR(13) or use the combined formula that removes both.

How do I count the number of lines in a cell?

Add 1 to the line break count: =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1. This works because each line break separates two lines, so the number of lines is one more than the number of breaks.

Can I count line breaks in a cell that contains formulas?

Yes, the formula counts the resulting text in the cell, not the formula itself. If the formula returns text with line breaks, the count will reflect that.

What if my cell has both line breaks and other characters like commas?

The formula only counts line breaks, ignoring all other characters. If you need to count commas as well, you can use a similar approach with CHAR(44).

Is there a way to count line breaks without a formula?

You can use the Find and Replace feature. Press Ctrl+H (Cmd+H on Mac), in “Find what” type Ctrl+J (which inserts a line break), leave “Replace with” empty, and click “Replace All”. Excel will show a dialog with the number of replacements. However, this modifies your data, so use it on a copy.

Does this formula work in Excel Online?

Yes, Excel Online supports CHAR(10) and the same functions, so the formula works there as well.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.