# How to count the number of lines in a cell in Excel

Count the line breaks and add one: =IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1).

**Excel:** `=IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Delivery notes | Lines |
| Leave at front desk Call on arrival |  |
| Fragile |  |
| Gate code 4471 Building B Third floor |  |
|  |  |
| Deliver after 5pm No signature needed Use side door Dog in yard |  |

Formula in B2, filled to B6: `=IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1)`

Result: 2; 1; 3; 0; 4

## How it works

Each new line in a cell starts after a line break, `CHAR(10)`. Removing them all and comparing lengths gives the number of breaks, and the number of lines is one more. The IF keeps an empty cell at 0.

## Other ways

- `=IF(A2="",0,COUNTA(TEXTSPLIT(A2,CHAR(10))))`: Excel 365: split the cell at each line break and count the pieces.

## Common mistakes

- Lines created by Wrap Text aren't real line breaks and aren't counted; only Alt+Enter (or CHAR(10) in a formula) creates one.
- A line break at the very end adds an empty line to the count. Trim it first with a SUBSTITUTE or check `RIGHT(A2)=CHAR(10)`.

## Questions

**How do I split the lines into separate cells?**

Use `=TEXTSPLIT(A2,CHAR(10))` for columns or `=TEXTSPLIT(A2,,CHAR(10))` for rows.

**Does this work in Google Sheets?**

Yes. Google Sheets uses CHAR(10) for line breaks too.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-lines-in-a-cell