Text
Add line breaks with CHAR(10) and extract the last line in a cell
Join status updates with newline delimiter CHAR(10), count lines, and pull the most recent bottom line with TEXTAFTER(..., CHAR(10), -1).
CHAR(10) is the spreadsheet newline character
CHAR(10) supplies a line-feed character for the illustrated spreadsheet text. Imported Windows line endings may contain an additional CHAR(13), which should be normalized separately.
TEXTJOIN(CHAR(10), TRUE, A2:C2) stacks non-empty status steps into a multi-line audit trail, and IFERROR(TEXTAFTER(log, CHAR(10), -1), log) extracts the latest status line at the bottom (falling back cleanly when only 1 line exists).
The count includes embedded empty lines, even though TEXTJOIN skips empty source cells. Last means physically last, not necessarily most recent by timestamp. Keep each source step to one line if counting steps rather than lines.
| Step1 (A) | Step2 (B) | Step3 (C) | Line Count | Last Line |
|---|---|---|---|---|
| 2026-04-01 Created | 2026-04-03 Reviewed | 2026-04-08 Approved | 3 | 2026-04-08 Approved |
| 2026-04-01 Created | (blank) | (blank) | 1 | 2026-04-01 Created |
Extract latest line and count total lines
Enable Wrap Text on cells returning CHAR(10) so each line renders on its own visual row.
=LET(log, TEXTJOIN(CHAR(10), TRUE, A2:C2), IFERROR(TEXTAFTER(log, CHAR(10), -1), log))Count lines in the joined text
Three joined steps contain 2 newline characters; 2 + 1 = 3 lines.
=LET(log, TEXTJOIN(CHAR(10), TRUE, A2:C2), IF(log="", 0, LEN(log) - LEN(SUBSTITUTE(log, CHAR(10), "")) + 1))Try the example
Use the original values or change them to test your understanding.