Text formula page
Remove Line Breaks Formula
Use this when pasted CSV, CRM, or form data includes line breaks inside cells.
Remove line breaks from spreadsheet cells.
The formula returns the same text with carriage returns and line feeds replaced by spaces.
Copy formulas
=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " ")=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " ")Excel and Google Sheets can replace both common line-break codes with spaces. The optional TRIM wrapper then removes repeated ordinary spaces created during cleanup.
Example data
| Raw Text | Example Result |
|---|---|
| Maya Chen | Maya |
| SKU-1001-East | SKU |
| https://www.example.com/pricing | example.com |
| Acme North | Acme North |
The formula returns the same text with carriage returns and line feeds replaced by spaces.
How the formula works
- CHAR(13) catches carriage returns.
- CHAR(10) catches line feeds.
- Nested SUBSTITUTE handles both common line break characters.
| Syntax piece | Role in the formula |
|---|---|
| A2 | The source cell containing text with one or more embedded line breaks. |
| CHAR(13) | The carriage-return character that can appear in imported line breaks. |
| CHAR(10) | The line-feed character used by many spreadsheet and pasted-text line breaks. |
| " " | The replacement space inserted where each line-break character was found. |
Verified examples
=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " ")Excel: Set A2 to the text Maya, then CHAR(10), then Chen. Returns: Maya Chen
=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " ")Google Sheets: Set A2 to the text East, then CHAR(13), then West. Returns: East West
=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " "))Excel: Set A2 to text containing a CRLF break between Maya and Chen. Returns: Maya Chen with single spaces
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A cleaned value contains two spaces | A CRLF pair supplied both CHAR(13) and CHAR(10), creating two replacement spaces. | Wrap the nested SUBSTITUTE formula in TRIM to collapse repeated normal spaces. |
| The cell wraps but the formula finds no line break | Visible text wrapping is a display setting, not necessarily a stored CHAR(10) or CHAR(13). | Inspect the actual cell content and use the formula only when a stored break exists. |
| Other invisible characters remain | SUBSTITUTE targets only the two line-break codes and does not remove every control character. | Use CLEAN for additional nonprinting characters, then TRIM if normal spaces also need collapsing. |
When not to use this formula
- Do not use this formula to parse structured CSV fields or to preserve intentional paragraph breaks.
Alternatives
| Alternative | When to use it |
|---|---|
| Remove Extra Spaces Formula | Use when the input has repeated ordinary spaces but no embedded line breaks. |
| CSV Column Cleaner | Use when a local browser cleanup workflow should process an entire CSV column. |
Related formulas
Official references
- SUBSTITUTE function from Microsoft
- CLEAN function from Microsoft
FAQ
Why replace both CHAR(10) and CHAR(13)?
Different pasted or imported sources use line feed, carriage return, or both as a CRLF pair, so replacing both handles the common variants.
What does TRIM add?
TRIM removes leading and trailing spaces and collapses repeated normal spaces after the line breaks have been replaced.