How to delete spaces in excel

Answer

TRIM removes extra spaces from text, leaving single spaces between words and removing leading and trailing spaces. 

How to remove leading, trailing, and extra spaces (use TRIM)

Use TRIM when you want to clean irregular spacing but keep single spaces between words.

1. Insert a helper column next to your data (for example, column B if your data is in A).

2. In the first helper cell enter: =TRIM(A2).

3. Press Enter and drag the fill-handle down to apply to the range.

4. Copy the helper column and Paste → Values on top of the original column to replace the dirty data.

TRIM targets the normal ASCII space (code 32) and collapses runs of those spaces. 

How to remove every space (no spaces left between words)

Use SUBSTITUTE when you need to delete every regular space character inside a string.

1. Insert a helper column.

2. Enter: =SUBSTITUTE(A2," ","").

3. Press Enter and fill down.

4. Copy the results and Paste → Values over the original cells if needed.

SUBSTITUTE replaces exact characters you specify, so it removes every regular space when you replace " " with "". 

How to remove nonprinting or hidden characters

Use CLEAN to strip many nonprinting characters that TRIM and SUBSTITUTE do not remove.

1. Use =CLEAN(A2) to remove common nonprinting ASCII characters (codes 0–31).

2. Combine with TRIM to remove extra spaces as well: =TRIM(CLEAN(A2)).

3. Copy and Paste → Values when finished.

CLEAN removes the first 32 nonprinting ASCII characters; some Unicode nonprinting characters require extra handling. 

How to remove nonbreaking or special spaces (CHAR(160))

Some imported data contains nonbreaking spaces (CHAR(160)) that TRIM ignores. Replace them with normal spaces, then TRIM.

1. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

2. Fill down and Paste → Values to finalize.

Replacing CHAR(160) with CHAR(32) (a normal space) lets TRIM collapse them properly. 

How to remove spaces using Find & Replace

Find & Replace deletes spaces quickly across ranges when you want to remove all space characters.

1. Select the range to change or click a sheet header to select all cells.

2. Open Replace: press Ctrl + H on Windows or Command + H on Mac.

3. In Find what type a single space (press Spacebar once).

4. Leave Replace with blank.

5. Click Replace All.

Find & Replace removes every instance of the exact character you enter, so use it when you want every space removed from the selected area. Keyboard shortcut differences are standard: Ctrl+H on Windows and Command+H on Mac. 

How to remove spaces inside numbers (preserve numeric type)

Numbers with spaces may be stored as text. Convert cleaned text back to numbers:

1. Remove spaces with =SUBSTITUTE(A2," ","").

2. Wrap with VALUE to convert: =VALUE(SUBSTITUTE(A2," ","")).

3. Fill down and Paste → Values.

VALUE converts a text string that looks like a number into a numeric value. Use this when numeric calculations must follow. 

When should I combine methods?

Use a combination when data is messy.

1. Use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to 1) replace nonbreaking spaces, 2) remove nonprinting chars, and 3) collapse extra normal spaces.

2. Fill down and Paste → Values once data is clean.

Combining functions handles more edge cases than any single function. 

Can I remove spaces for an entire workbook at once?

Yes. Use Find & Replace while the workbook is active and the search target is set to Workbook.

1. Press Ctrl + H (Windows) or Command + H (Mac).

2. Click Options and set Within: Workbook.

3. Enter a single space in Find what and leave Replace with blank.

4. Click Replace All.

Use workbook-wide replace with caution; it changes every matching cell. 

What if TRIM doesn’t remove spaces, I see?

TRIM may not remove nonbreaking or other special spaces; run SUBSTITUTE with CHAR(160) or CLEAN first, then TRIM. 

How to keep formulas from breaking when you Paste → Values

Use these steps to safely replace original data with cleaned results.

1. After formulas return cleaned text, select helper column.

2. Press Ctrl + C (Windows) or Command + C (Mac).

3. Right-click target column → Paste Special → Values.

4. Delete helper column.

Pasting values replaces formulas with their outputs so subsequent edits do not recalc or reintroduce old data.

What differences exist between Windows and Mac?

Find & Replace and most functions behave identically across Excel for Windows and Excel for Mac. Keyboard shortcuts differ:

1. Replace dialog: Ctrl + H on Windows, Command + H on Mac.

2. Find dialog: Ctrl + F on Windows, Command + F on Mac.

Ribbon locations for functions are the same in modern Excel versions. 

Best-practice checklist

1. Back up your workbook before mass replacements.

2. Work on a copy column and Paste → Values over originals only after verifying results.

3. Combine SUBSTITUTE, CLEAN, and TRIM for imported or web-scraped data.

4. Use VALUE when cleaned text should be numeric.

5. Test on a small sample before Replace All across a workbook.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report