How to Remove Spaces in Excel: 5 Good Methods

I once got a spreadsheet with 500+ entries that looked fine initially but wouldn’t calculate totals correctly. I didn’t know why, but later, it turned out that hidden spaces were causing all sorts of trouble. If you handle data in Excel, you may have encountered similar situations. These spaces often come when you import data from other sources.  

In this guide, I’ll show you different methods to remove unwanted spaces in Excel. You'll get step-by-step instructions and practical examples for each method to save you the headaches I went through. However, if you’re just starting, check out our Data Analysis in Excel course or the Excel Fundamentals skill track. 

Understanding Spaces in Excel

Before we remove spaces, let’s understand the types of spaces that may be lurking in your data:

• Leading spaces: Spaces at the start of a cell.
• Trailing spaces: Spaces at the end of a cell.
• Extra spaces between words: More than one space between words.
• Non-breaking spaces: Special spaces that look normal but behave differently.

These spaces can slip into your data from importing files, manual entry mistakes, inconsistent formatting, or even copying and pasting from other sources.

Types of spaces. Image by Author. 

Different Ways to Remove Spaces in Excel

Though spaces may seem minor, they can cause big problems. Formulas might not work, comparisons may fail, and calculations could turn out wrong. Before jumping into solutions, I’d recommend the following:

• Back up your data: Always make a copy of your data before making changes. This ensures you can recover the original data if something goes wrong.
• Check formatting: Ensure consistent formatting in your cells, such as text case and number formats, for cleaner and more reliable results.

Now, let’s explore the best methods to remove spaces in Excel.

Method 1: Use the TRIM() function to remove extra spaces

The TRIM() function removes unnecessary spaces, such as leading, trailing, and multiple spaces between words, except for the single-space character. Here's how to use it:

1. Create a helper column next to your data.

2. In the first cell of the helper column, enter the formula: =TRIM(A2).

3. Press Enter and drag the formula down to apply it to all rows.

Use TRIM() to remove the spaces. Image by Author.

Method 2: Use the SUBSTITUTE() function

The SUBSTITUTE() function removes every space (including the space between words) from a cell and replaces any character, including spaces, with something else. It cleans numerical data, such as removing spaces that separate digits. 

Its basic syntax is:

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

Here’s how to apply it and remove all the spaces: 

1. Create a helper column.

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

3. Press Enter and drag it down to apply it to all rows.

Using the SUBSTITUTE() function. Image by Author.

Method 3: Data cleanup with Find & Replace

If you want a quick and easy fix, I’d recommend Excel’s Find & Replace tool — it can handle extra spaces between words. Here’s how:

1. Select your data range.

2. Press Ctrl+H to open the Find & Replace dialog.

3. In the Find what field, enter two spaces.

4. In the Replace with field, enter a single space.

5. Click Replace All.

Cleaning spaces using the Find & Replace feature. Image by Author.

Repeat the steps until Excel shows the message, We couldn't find anything to replace. Note that this method reduces leading and trailing spaces to a single space but does not remove them completely. It only addresses spaces between words. 

Method 4: Use the CLEAN() function to remove non-printing characters

When we import or copy data from external sources, we often get non-printable characters that can disrupt data operations. You can use the CLEAN() function to remove them or, even better — combine it with TRIM() to clean up extra spaces.

Here’s how you can do it: 

1. Enter the formula: =TRIM(CLEAN(A2)) in a helper column.

2. Press Enter and drag the formula down.

Combine TRIM() and CLEAN() to remove spaces. Image by Author.

Method 5: Advanced method – use Excel Add-Ins for faster results

You can use the third-party add-in Ablebits Ultimate Suite for Excel. Its Trim Spaces tool cleans up data quickly. With one click, it takes care of everything. This includes leading and trailing spaces, line breaks, extra blanks between words, and non-breaking spaces. So, if you’re like me who works with large datasets regularly, it can save you so much time. 

Here is how to use it:

1. Select the range of cells to clean.

2. Open the Ablebits Data tab and click Trim Spaces.

3. From the options pane, select what you want to remove and click Trim.

Trim spaces with Excel’s add-in. Image by Author.

Specific Solutions for Common Space Removal Scenarios in Excel

Now, let’s break look at a few common scenarios you may run into and how you can quickly fix them.

Remove extra spaces

Extra spaces can make your data look messy and hard to work with. You can use the TRIM() function to clean them up while keeping one space between words. For example, I have a couple of data entries with inconsistent spacing. To sort this, I entered the following formula in cell B2 and applied it to all rows:

=TRIM(A2)

Use TRIM() to remove extra spaces. Image by Author.

Remove leading spaces 

Sometimes, we have to keep multiple spaces between words to make our data easier to read, but remove any spaces from the start. The TRIM() function works well but removes all the spaces in the text. So, in such a case where you want to remove only the leading spaces while keeping the rest intact, use this advanced formula:

=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))

Here’s how the formula works: It locates the first non-space character and keeps everything from that point forward. This way, it keeps spaces between words and trims only at the start of the cell (leading spaces).

Remove only leading spaces. Image by Author.

Remove trailing spaces

Trailing spaces are extra spaces that appear after text or numbers in a cell. These invisible characters can cause problems like formula errors, where cells that appear identical fail to match in functions like VLOOKUP() or MATCH(). To avoid this, use the TRIM() function. For example, I put this formula in cell B2 and then applied it to all rows to remove extra spaces from my data.

=TRIM(A2)

Removing trailing spaces. Image by Author.

Remove spaces before numbers

While TRIM() works great for cleaning up spaces in text, you may face issues using it with numeric data. Let me show you what happens:

=TRIM(A2)

The TRIM() function doesn’t remove the spaces correctly. Image by Author.

You can see, the TRIM() removed spaces before a number, but Excel doesn't treat the result as a number anymore — it treats the outcome as text. Here’s how to spot this:

• The data is left-aligned instead of right.

• Look at your status bar - it only shows you how many cells you've selected (COUNT), not their total. 

• If you try to add up these cells with SUM(), you'll get zero.

You need to turn the text back into a number to fix this. To do so, you can either multiply by 1 or use this formula:

=VALUE(TRIM(A2))

This removes spaces and ensures Excel treats the data as numbers.

Combine VALUE() and TRIM() to remove spaces before numbers. Image by Author.

Remove spaces from a string 

If you want to remove all spaces, including those between words, use the SUBSTITUTE() function:

=SUBSTITUTE(A2, " ", "")

You can see the formula cuts off every space from the string.

Remove the spaces using the SUBSTITUTE() function. Image by Author.

Remove special characters and unnecessary spaces  

SUBSTITUTE() is great when you want to remove specific characters from your text. But you can pair it with TRIM() to clean up special characters and spaces in one go. For example, if I want to remove & and clean up spaces, I use this formula:

TRIM(SUBSTITUTE(A2, “&“, “”))

You can see how TRIM() and SUBSTITUTE() work when used together versus SUBSTITUTE() alone.

Combine TRIM() and SUBSTITUTE() to clean spaces. Image by Author.

Common Problems and Solutions

Sometimes, when I copy or import data from other sources, a lot of non-breaking spaces and strange characters appear in my data. Here’s how I handle them: 

Non-breaking spaces that TRIM() doesn’t handle

The TRIM() function removes extra spaces, but it doesn’t work for non-breaking spaces (character code 160). These often show up in data from web pages or formatted sources. To fix this, I use:

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

This formula replaces non-breaking spaces with standard spaces that TRIM() can remove.

Hidden characters or pasted data problems

Sometimes, other hidden characters sneak in, like tabs or line breaks. To find them, I use CODE() to check the character’s position:

• First character: =CODE(LEFT(A1,1))

• Last character: =CODE(RIGHT(A1,1))

• Specific position: =CODE(MID(A1, n, 1)) (Replace n with the position number.)

This is the formula I use:

(RIGHT(C2))

Once I know the code, I remove it with TRIM() and SUBSTITUTE(). For example, to remove tabs (code 115), I use:

(SUBSTITUTE(C2, CHAR(115), “ “))

Use CODE(), TRIM(), and SUBSTITUTE() to remove spaces. Image by Author.

Final Thoughts

In this guide, I showed you methods like TRIM() for simple fixes, SUBSTITUTE() for advanced problems, and add-ins in case you will be removing spaces frequently and want a different process. Each method suits different data types, so choose the one that fits your needs.

If you want to strengthen your skills further, check out the Datacamp Data Analysis with Excel Power Tools skill track. It offers step-by-step lessons to help you master Excel and improve your data-cleaning techniques.