Text
Count line breaks, build multiline cells & extract specific lines with CHAR(10)
Count line breaks and lines inside Alt+Enter cells using LEN - LEN(SUBSTITUTE(A2, CHAR(10), "")), extract the Nth line via TEXTSPLIT, and join lines with CHAR(10).
Work with Alt+Enter multiline cells cleanly using ASCII line feed CHAR(10)!
A line-feed character CHAR(10) separates logical lines in this example. Imported text may instead use CRLF or standalone carriage returns. Normalize CRLF and CR to LF before counting or extracting. Visual wrapping at the column edge does not add logical line breaks.
An empty cell or empty-string formula has zero lines; numeric zero is a one-line value. Consecutive separators and a trailing separator represent empty lines and are counted. Extracting line two returns No second line if it does not exist, and an empty string when the second line exists but is empty.
| Task | Formula Pattern | Cell Value ("SKU-104\nQty: 25\nShip: Express") | Result |
|---|---|---|---|
| Count Line Breaks | LEN(A2) - LEN(SUBSTITUTE(A2, CHAR(10), "")) | Two LF separators | 2 breaks |
| Count Total Lines | IF(A2="", 0, Breaks + 1) | 2 + 1 (0 if empty) | 3 lines |
| Extract 2nd Line | INDEX(TEXTSPLIT(A2, CHAR(10)), 1, 2) | Middle line token | "Qty: 25" |
| Build Multiline Label | B2 & CHAR(10) & C2 | Joins fields with line break | 2-line address/tag |
Count total lines in a multiline cell (with blank guard)
COUNTBLANK distinguishes empty content from numeric zero. A2&"" converts a nonempty numeric value to text before normalization.
=IF(COUNTBLANK(A2)>0,0,LET(noteText,SUBSTITUTE(SUBSTITUTE(A2&"",CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10)),LEN(noteText)-LEN(SUBSTITUTE(noteText,CHAR(10),""))+1))Extract the Nth line or assemble a multiline cell with CHAR(10)
Normalize line endings and split on LF into columns. The explicit line-count guard distinguishes a missing second line from a blank second line. Enable cell text wrapping to display embedded line breaks.
=IF(COUNTBLANK(A2)>0,"No second line",LET(noteText,SUBSTITUTE(SUBSTITUTE(A2&"",CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10)),IF(LEN(noteText)-LEN(SUBSTITUTE(noteText,CHAR(10),""))<1,"No second line",INDEX(TEXTSPLIT(noteText,CHAR(10)),1,2))))Try the example
Use the original values or change them to test your understanding.