How to Remove Spaces in Excel

Why Spaces Break Excel Formulas

Excel compares text values character by character. The text ABC and the text ABC (with a trailing space) are different values. As far as Excel is concerned, they are as different as ABC and XYZ.

This has real consequences:

• VLOOKUP and XLOOKUP return #N/A when the lookup value has a trailing space but the table does not (or vice versa).
• SUMIF and COUNTIF return wrong totals because "East" and "East " count as two different criteria values.
• Duplicate detection misses matches because "John Smith" and "John Smith " are not flagged as duplicates.
• Data validation dropdowns show the same value twice — once with a space, once without.
• PivotTables group the same category into two separate buckets.

Removing spaces is one of the highest-impact and easiest data cleaning steps you can take before analysis.

Types of Spaces in Excel

Not all spaces are the same character:

• Regular space (CHAR 32): The normal space you type. TRIM removes extra ones.
• Non-breaking space (CHAR 160): Used in web content and some applications to prevent line breaks. Looks identical to a regular space on screen but is a different character. TRIM does NOT remove these.
• Non-printing characters (CHAR 1–31): Includes line breaks (CHAR 10), carriage returns (CHAR 13), and tab characters (CHAR 9). Often present in data copied from PDFs, web pages, or databases. CLEAN removes most of these.

This distinction is critical because the fix depends on which type of space you are dealing with.

Method 1: TRIM — Remove Leading, Trailing, and Extra Spaces

=TRIM(A2)

TRIM removes all leading spaces (before the first character), all trailing spaces (after the last character), and reduces any run of multiple consecutive spaces between words to a single space.

It does NOT remove single spaces between words — TRIM("John Smith") remains "John Smith". It does NOT remove non-breaking spaces (CHAR 160).

TRIM is the right function for:

• Names with leading or trailing spaces from form submissions.
• City and country names that have extra spaces between words.
• Product categories copied from messy spreadsheets.

Method 2: CLEAN — Remove Non-Printing Characters

=CLEAN(A2)

CLEAN removes non-printing characters with ASCII values 1 through 31. This includes line breaks, carriage returns, tab characters, and other control characters that often appear in data copied from PDFs, web pages, terminal outputs, or old database exports.

Combine TRIM and CLEAN together for a thorough cleanup of imported data:

=TRIM(CLEAN(A2))

This handles both extra spaces and non-printing characters in one formula. It is a good default formula to apply to any column you receive from an external source before using it in formulas or analysis.

Method 3: SUBSTITUTE — Remove Non-Breaking Spaces

Non-breaking spaces (CHAR 160) are the trickiest type because they look identical to regular spaces. The simplest way to detect them: if TRIM does not work and the cell still looks like it has leading or trailing spaces, you probably have CHAR 160.

Replace non-breaking spaces with regular spaces first, then TRIM:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

This substitutes the CHAR 160 non-breaking space with a regular CHAR 32 space, then TRIM cleans up any extra ones.

The Best Combined Formula for Messy Imports

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Apply this to any column that comes from an external source and you are unsure of its quality. It handles:

• Regular extra spaces (TRIM)
• Non-printing characters like line breaks (CLEAN)
• Non-breaking spaces from web and PDF content (SUBSTITUTE + CHAR 160)

This is the most comprehensive single-formula cleanup available without VBA or Power Query.

Method 4: SUBSTITUTE — Remove All Spaces

=SUBSTITUTE(A2," ","")

This removes every space character from the value — including spaces between words. Use it carefully: it is right for product codes, phone numbers, account numbers, and IDs, but wrong for names or sentences where spaces are meaningful.

Example use cases where removing all spaces is correct:

• Phone number formatting: =SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","") removes spaces and hyphens from phone numbers for database storage.
• Product codes: =SUBSTITUTE(A2," ","") removes spaces from inconsistently formatted SKUs.
• Account numbers: useful when different systems store the same number with or without spaces.

Method 5: Find and Replace

For a quick one-time fix without formulas:

1. Press Ctrl + H to open Find and Replace.
2. In Find what, type a space character.
3. Leave Replace with empty.
4. Click Replace All.

This removes every space from the selected cells, including spaces between words. If you want to remove only leading and trailing spaces (but keep spaces between words), this is not the right approach — use a TRIM formula instead.

To find and replace non-breaking spaces via Find and Replace: in the Find what field, hold Alt and type 0160 on the numeric keypad (this inserts CHAR 160). Leave Replace with blank and click Replace All.

Method 6: Power Query Trim and Clean

If you clean data from the same source regularly, Power Query is the most efficient approach because the cleanup runs automatically on every refresh:

1. Load data into Power Query via Data > From Table/Range.
2. Select the column to clean.
3. Go to Transform > Format > Trim to remove leading and trailing spaces.
4. Go to Transform > Format > Clean to remove non-printing characters.
5. Click Close & Load to push the clean data back to Excel.

Power Query's Trim only removes leading and trailing spaces (not extra spaces in the middle like Excel's TRIM does). For middle-space cleanup in Power Query, use a custom column with Text.Trim(Text.Clean([ColumnName])).

How to Apply Cleanup Formulas Without Formulas in the Final File

Once you have a helper column with the cleaned values, convert them to plain text so you can delete the original dirty column:

1. Select the helper column with the cleanup formula.
2. Copy with Ctrl + C.
3. Right-click in the same location and choose Paste Special > Values.
4. The column now contains plain text — the formula is gone.
5. Delete the original dirty column.

How to Detect Spaces in a Column Quickly

Before cleaning, you can verify whether a column has space problems:

• Add a helper formula: =LEN(A2)<>LEN(TRIM(A2)). TRUE means the cell has extra spaces that TRIM would remove.
• Use COUNTIF to check: =COUNTIF(A:A,"* ") counts cells with trailing spaces. =COUNTIF(A:A," *") counts cells with leading spaces.
• Use Column Statistics to inspect a column for common data quality issues including inconsistent whitespace.

After Cleaning: Run a Duplicate Check

After removing spaces, values that previously appeared different may now be identical. For example, "East" and "East " were two different values before cleaning and are the same value after. After cleaning a column, always run a duplicate check to catch newly matched values that should be reviewed before analysis.

Frequently Asked Questions

How do I remove spaces from numbers in Excel?

Numbers with spaces are stored as text. Use SUBSTITUTE to remove the spaces: =VALUE(SUBSTITUTE(A2," ","")). The VALUE function converts the result back to a real number. Without VALUE, the result remains text even though it looks like a number.

Why does my cell still have a space after applying TRIM?

TRIM does not remove non-breaking spaces (CHAR 160). These come from web pages, HTML content, and some software exports. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to handle them. If spaces are still visible after this formula, use =CODE(LEFT(A2,1)) to find the character code of the first character and identify what character is actually present.

How do I remove line breaks from Excel cells?

Line breaks in cells are CHAR(10) on Windows and CHAR(13) on Mac. Use CLEAN to remove them: =CLEAN(A2). Or use SUBSTITUTE to replace line breaks with a space instead of nothing: =SUBSTITUTE(A2,CHAR(10)," "). This preserves word boundaries when a line break was used instead of a space.

Does TRIM work on numbers?

TRIM converts numbers to text when you apply it. Use VALUE to convert back: =VALUE(TRIM(A2)). If the number had no spaces to begin with, TRIM just returns a text version of the number. For numbers, it is usually better to use Text to Columns to clean them rather than TRIM.