How to Remove Carriage Returns in Excel Using CLEAN and SUBSTITUTE

Managing imported database exports often frustrates analysts when hidden carriage returns break formulas and disrupt reporting. While standard text cleanup tools like the TRIM function resolve basic spacing issues, they fail to eliminate these stubborn, non-printing line breaks.

Integrating a advanced formula grants immediate data compatibility, saving hours of manual deletion. However, a key educational stipulation is that the CLEAN function alone cannot remove all stubborn spacing characters; it must be paired strategically. For instance, nesting SUBSTITUTE to target CHAR(13) (carriage return) and CHAR(10) (line feed) ensures complete removal.

Below, we outline the exact nested formula sequence to automate this database cleanup process.

Excel is an incredibly powerful tool for data analysis, but it often falls victim to a classic data-cleaning headache: messy formatting. When importing data from external databases, web scrapes, PDF files, or CRM exports, you will frequently find hidden characters embedded within your text. Among the most troublesome of these are carriage returns and line breaks.

These invisible characters break your rows, make your sheets look disorganized, and catastrophically disrupt lookup formulas like VLOOKUP, XLOOKUP, and MATCH. Fortunately, Excel provides built-in tools to resolve this. By mastering the CLEAN and SUBSTITUTE functions-and knowing how to combine them-you can easily sanitize your spreadsheets and restore data integrity.

Understanding the Culprits: Carriage Returns and Line Feeds

Before jumping into the formulas, it helps to understand what you are actually trying to remove. What we commonly call a "line break" is usually one of two hidden control characters in ASCII code:

• Line Feed (LF): Represented in Excel by CHAR(10). This is the standard line break used in Excel when you press Alt + Enter to start a new line within a cell. It is also the standard line-ending character in Unix and macOS systems.
• Carriage Return (CR): Represented in Excel by CHAR(13). Historically used by typewriters to return the carriage to the start of a line, this character is still heavily used in Windows-based text files and legacy database systems, often paired directly with a Line Feed (CRLF).

Because these characters are non-printing, they may look like normal spaces, cause text to wrap unpredictably, or hide completely until you double-click a cell.

Method 1: The Quick Fix with the CLEAN Function

Excel features a dedicated function designed to remove non-printable characters: the CLEAN function. Its syntax is incredibly straightforward:

=CLEAN(text)

The CLEAN function goes through your target cell and automatically strips out the first 32 non-printing characters in the 7-bit ASCII set (values 0 through 31), which conveniently includes both CHAR(10) (Line Feed) and CHAR(13) (Carriage Return).

The Limitation of CLEAN

While CLEAN is fast, it has a significant drawback. It deletes the line break characters entirely without replacing them with anything. This can result in words getting mashed together. For example, if a cell contains:

John Watson
221B Baker Street
    

Applying =CLEAN(A1) will output:

John Watson221B Baker Street

Notice how "Watson" and "221B" have run together because the line break separating them was deleted instead of being converted into a space. To prevent this, we need a more surgical approach using the SUBSTITUTE function.

Method 2: Replacing Breaks with Spaces Using SUBSTITUTE

To keep your words separated, you can use the SUBSTITUTE function to find specific line break characters and replace them with a standard space character (" ").

The syntax for SUBSTITUTE is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Replacing Line Feeds (CHAR(10))

To target standard Excel line breaks (Line Feeds), use this formula:

=SUBSTITUTE(A1, CHAR(10), " ")

This replaces every instance of a line break in cell A1 with a single space, maintaining the legibility of your addresses, notes, or descriptions.

Replacing Carriage Returns (CHAR(13))

If your data originated from an external Windows application or database export, it may contain Carriage Returns. You can target these specifically using:

=SUBSTITUTE(A1, CHAR(13), " ")

Method 3: The Ultimate Combo Formula

In real-world data cleaning, you rarely know whether your imported text contains just Carriage Returns, just Line Feeds, or a messy combination of both. To make your spreadsheet bulletproof, you should nest multiple SUBSTITUTE functions together and wrap them in TRIM and CLEAN.

Here is the ultimate data-cleaning formula for Excel:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13), " "), CHAR(10), " ")))

How This Nested Formula Works (Step-by-Step)

1. SUBSTITUTE(A1, CHAR(13), " "): The innermost function sweeps cell A1 for any Carriage Returns (ASCII 13) and replaces them with a space.
2. SUBSTITUTE(..., CHAR(10), " "): The next layer takes that output and searches for any Line Feeds (ASCII 10), replacing those with a space as well. This ensures that any combined CRLF line breaks are cleanly converted to spaces instead of running words together.
3. CLEAN(...): This function then takes the text and sweeps for any remaining low-level, non-printable characters (ASCII 0 to 31) that might still be lingering in your dataset.
4. TRIM(...): Finally, the TRIM function cleans up the aesthetic side of the text. Because we replaced line breaks with spaces, we may have accidentally created double spaces or left trailing/leading spaces. TRIM automatically collapses multiple consecutive spaces into a single space and strips spaces from the very beginning and very end of the string.

Handling Web Data and Non-Breaking Spaces (CHAR(160))

Sometimes, even after running the ultimate combination formula above, you might notice that some spaces or formatting anomalies refuse to budge. This is common when copying data from web browsers, which frequently use a "Non-Breaking Space" (HTML entity  ), represented in Excel as CHAR(160).

Because CHAR(160) is outside the standard 0–31 ASCII range, the CLEAN function completely ignores it. To eliminate these stubborn spaces, you can add another nested SUBSTITUTE to convert them into regular spaces (CHAR(32)) before trimming:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, CHAR(160), " "), CHAR(13), " "), CHAR(10), " ")))

By transforming non-breaking spaces into normal spaces, the outer TRIM function can finally do its job and clean up your data flawlessly.

An Alternative: Find and Replace (No Formulas Needed)

If you prefer a quick, one-off solution without writing formulas or creating helper columns, you can use Excel's native Find and Replace dialog box. This is particularly useful for rapid, sheet-wide cleaning.

1. Select the range of cells or columns you want to clean.
2. Press Ctrl + H to open the Find and Replace dialog box.
3. Click into the Find what input box.
4. Press Ctrl + J on your keyboard. Note: Nothing obvious will appear in the box, but you might see a tiny blinking dot or a slight change in the cursor. This shortcut inserts the Line Feed character (ASCII 10).
5. Click into the Replace with box and type a single space (or leave it completely empty if you want to delete the breaks entirely).
6. Click Replace All.

This trick is a lifesaver for quickly wiping out vertical line breaks across thousands of rows in seconds.

Summary of Solutions

Conclusion

Carriage returns, line feeds, and hidden non-printing characters can turn an otherwise clean dataset into an analytical nightmare. By utilizing Excel's robust text-manipulation functions like CLEAN and SUBSTITUTE, you can build dynamic formulas that handle these edge cases effortlessly. Whether you choose the formula-based nested approach for repeatable templates, or the quick Ctrl + J shortcut for manual data prep, you now have the tools to keep your spreadsheets clean, professional, and fully compatible with downstream calculations.