Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count the lines in a cell in Excel and Google Sheets
Topic:Text manipulationExcel & Google Sheets
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Notes |
| Line one |
| First line Second line |
| A B C |
| Single |
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1→1
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the cell containing multi-line text (line breaks inserted with Alt+Enter on Windows, Control+Option+Enter on Mac, or Control+Enter in Sheets).
- 2LEN(A2) counts all characters including line break characters.
- 3SUBSTITUTE(A2,CHAR(10),"") removes all line breaks.
- 4The difference LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),"")) equals the number of line breaks.
- 5Adding 1 converts line-break count to line count (n breaks = n+1 lines).
- 6Full formula: =LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1.
Tips
- If you want 0 for an empty cell rather than 1, wrap with IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if line breaks are CHAR(13) on some systems?
Excel Windows uses CHAR(10); some imported data may use CHAR(13) or both. Count both: =LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),""))+1.
Can I split multi-line cell content into separate rows?
In Google Sheets use =SPLIT(A2,CHAR(10),TRUE,TRUE). In Excel 365 use =TEXTSPLIT(A2,,CHAR(10)) to split on line breaks.
More on Text manipulation
See all →How-to
How to split text into columns
Split a full name or any delimited text into separate columns. Google Sheets uses SPLIT; Excel uses TEXTSPLIT — here is each.
How-toHow to remove extra spaces
Clean leading, trailing, and double spaces with TRIM — the usual fix for lookups that fail on values that look identical.
How-toHow to convert text to a number
Turn numbers stored as text into real numbers with VALUE or by multiplying by 1 — the fix for sums that ignore values.
How-toHow to extract the domain from an email address
Pull everything after the @ from an email using FIND and MID. Works the same in Excel and Google Sheets.
How-toHow to extract the first name from a full name
Get the first name from a full name with LEFT and FIND, splitting on the first space. Works in Excel and Google Sheets.
How-toHow to add leading zeros
Pad numbers to a fixed width with leading zeros using TEXT, or a custom number format. Works in Excel and Google Sheets.