Home > Formulas > Text > Remove Spaces

Remove Spaces in Excel Cells

How to remove spaces in Excel, using TRIM, SUBSTITUTE, find and replace, macros. Videos, written steps, sample file. Avoid problems with sort, filter, lookup

TRIM function removes start and end spaces

Remove Spaces From Excel Cells

In some Excel worksheets, there might be extra spaces in some cells, that you want to remove. For example, imported data, from a website, or another computer system, might contain leading or trailing spaces, or extra spaces between words.

Those extra spaces can cause problems when you try to sort or filter the data, if you're using lookup functions, such as VLOOKUP, MATCH or XLOOKUP. Fortunately, Excel has built-in functions and commands that can help you find and remove excess spaces.

In the sections below, you'll see how to trim spaces in Excel, with:

--1) TRIM function

--2) SUBSTITUTE function

--3) CLEAN function

--4) Find and replace command

--5) Excel macros

Formula Methods

With the TRIM and SUBSTITUTE functions, you can put the formulas in a helper column, beside the original text. This will create a column with the trimmed text, and the original data will remain unchanged.

The formula methods are helpful if the original data will change, or you copy and paste new data onto the worksheet frequently.

The formula results could be copied, and pasted as values into another location, use a Paste Special command.

Commands or Macros

If you use the Find and Replace command, or one of the Excel macros, the original data will be changed.

As a precaution, be sure to make a backup copy of the original data, before you use one of these methods to trim spaces in the data.

1) Excel TRIM Function

The Excel TRIM function can help with the cleanup of text that you've downloaded from a website, or imported from another application.

In this video you'll see how to use the TRIM function to:

  • remove spaces from the start and end of a text string
  • remove all except single spaces between words
  • TRIM does NOT remove some special space characters that have been copied from websites.

There are written steps below the video.

TRIM Syntax

The TRIM function has the following syntax:

  • TRIM(text)
    • text is a cell reference, or text string, from which you want spaces removed.

trim function

TRIM Ex 1: Remove Start/End Spaces

The TRIM function will remove all the space characters at the start and end of a text string.

For example, in cell C5 in the screenshot below, there are:

  • 2 extra spaces at the start of the text
  • 2 extra spaces at the end of the text.

In cell E5, the LEN function calculates how many characters are in cell C5, and the result is 17 characters.

The TRIM formula in cell C7 removes those 4 spaces: =TRIM(C5)

In cell E7, the LEN function calculates the number of characters that are in cell C7, and the result is 13 characters -- the leading space characters and trailing space characters were removed.

trim function removes start and end spaces

TRIM Ex 2: Remove Extra Spaces

You can also use the TRIM function to remove extra space characters between words in a text string.

In the screenshot below, there are 3 extra spaces between the words in cell C5. In cell E5, the LEN function calculates how many characters are in cell C5, and the result is 20 characters.

The TRIM function in cell C7 removes those extra spaces, as well as the 2 spaces at the start and 2 spaces at the end of the text string.

=TRIM(C5)

In cell E7, the LEN function calculates how many characters are in cell C7, and the result is 13 characters -- the extra spaces between the words, and the spaces at the start and end were removed.

trim removes extra spaces

TRIM Ex 3: Non-Breaking Spaces

It's important to know that the TRIM function only removes regular spaces. It does NOT remove some space characters, such as non-breaking spaces copied from a website. The video below shows how to remove those characters.

For example, in this screenshot, cell C5 contains one non-breaking space. In cell E5, the LEN function calculates how many characters are in cell C5, and the result is 14 characters.

That non-breaking space is NOT trimmed by the TRIM function: =TRIM(C5)

In cell E7, the LEN function calculates how many characters are in cell C7, and the result is 14 characters -- the non-breaking space was not removed in the trimmed values.

TRIM cannot remove non-breaking space character

2) SUBSTITUTE Function

To remove the non-breaking space characters, we can use the SUBSTITUTE function, combined with the CHAR function. Here is the formula in cell C5:

  • =SUBSTITUTE(C4,CHAR(160),"")

SUBSTITUTE function with CHAR function

How It Works

The SUBSTITUTE function can remove a specific character, and replace it with a different character, or an empty string (nothing). The SUBSTITUTE function has the following 4 arguments in its syntax:

  • SUBSTITUTE(text, old_text, new_text, instance_num)

We'll use the first three arguments in our formula

  1. text: cell reference
  2. old_text: non-breaking space
  3. new_text: empty string - ""

Tip: To remove only a specific occurrence of the old text, use the 4th argument, instance_num)

CHAR Function

Behind the scenes in Excel, every character has a code number. For example:

  • Normal space characters are character 32
  • Non-breaking space characters are character 160

In our formula, CHAR(160) specifies that we want to remove non-breaking space characters.

CODE Function

Tip: To figure out what character is at the start of a cell, use the Excel CODE function. It returns the code number for the first character in a text string.

  • In the screen shot below, this formula in cell B2: =CODE(A2)
  • The first character in cell A2 is a non-breaking space, so the formula result is 160
  • In cell A3, the first character is a normal space, so the formula result in B3 is code 32

SUBSTITUTE function with CHAR function

Combine SUBSTITUTE and TRIM

If you want to clean cells that have extra spaces, AND non-breaking spaces, you can use the TRIM function, combined with the SUBSTITUTE function, in a nested formula.

Here is the formula in cell C11:

  • =TRIM(SUBSTITUTE(C10,CHAR(160),""))

The SUBSTITUTE formula removes any non-breaking spaces, and TRIM removes all other spaces, except for a single space between words.

SUBSTITUTE function with CHAR function

Video: SUBSTITUTE Function

In this video you'll see how to use SUBSTITUTE to change a region name in a report title, remove non-printing characters, and substitute a space character

There are more examples on the REPLACE or SUBSTITUTE page.

3) CLEAN Function

In addition to non-breaking characters, there are other non-printing characters in Excel, and some of those could also be in data imported from an external source.

To remove some non-printable characters, you can use the CLEAN function, alone, or combined with other functions.

  • Limitation: CLEAN function can only remove nonprinting characters with the first 32 code numbers (codes 0 to 31)
  • Benefit: CLEAN function remove all of those codes (0 to 31) at once. This is easier than using SUBSTITUTE, where you have to specify a single character code to remove.

CLEAN Examples

To test the CLEAN function, I created sample text which contains these non-printing characters:

  • line break - CHAR(10)
    • Note: When you press Alt+Enter in a cell, it creates a line break character
  • carriage return - CHAR(13)
  • non-breaking space - CHAR(160)

In columns D, E and F, formulas check for characters 10, 13 and 160, and return the position in which there were found (20, 30 and 1)

CLEAN Formulas

In rows 6, 7, and 8, three different formulas try to clean the text, to remove non-printing characters, non-breaking spaces, and extra spaces.

  • Formula A: First, in cell B6, the following formula removed characters 10, and 30, but not 160.
    • =CLEAN(B5)
  • Formula B: Next, to remove character 160, the formula in cell B7 combines CLEAN with SUBSTITUTE and CHAR:
    • =CLEAN(SUBSTITUTE(B5,CHAR(160),""))
  • Formula C: Finally, in cell B8, the TRIM function is added, to remove extra spaces:
    • =TRIM(CLEAN(SUBSTITUTE(B5,CHAR(160),"")))

So, in your Excel worksheets, use formulas that combine CLEAN, SUBSTITUTE and TRIM, if your data contains different types of non-printing characters, and extra spaces to remove.

CLEAN function combined with TRIM and SUBSTITUTE

4) Find and Replace Command

If you need to remove extra spaces between words on an Excel worksheet, the Find and Replace command can help you do that, without the need for any formulas.

Find and Replace Extra Spaces

To remove extra spaces between words on an Excel worksheet, using the Find and Replace command, follow these steps:

  • Select the cells that you want to clean up
    • Note: It's okay to include cells that do NOT have extra spaces -- they won't be affected
  • To open the Find and Replace window, press the keyboard shortcut Ctrl + H
  • In the Find What box, type two space characters
  • Press the Tab key on your keyboard, to move to the Replace With box
  • In the Replace With box, type one space character
  • Click Replace All

Find and Replace confirmation message

Excel shows a confirmation message, with the number of replacements that were made.

  • In the screen shot above, the message says: "All done. We made 16 replacements."

Click the OK button, to close the message, but don't close the Find and Replace window!

The cleanup might not be "All done".

Repeat the Replace All

When you're using this method, to replace two space characters with one space character, it's important to repeat the final step, and click the Replace All button again.

In some cells, there could be three or more space characters between two words, or at the start or end of the cell's text.

  • So, click Replace All again, to see the confirmation message.
  • Repeat this step, until you see a warning message, saying that there wasn't anything to replace.
    • "We couldn't find anything to replace. Click Options for more ways to search. FYI: It's possible the data you're trying to replace is in a protected sheet. Excel can't replace data in protected sheets."
  • When that message appears, click the OK button, to close the message
  • Then, in the Find and Replace dialog box, click the Close button

Find and Replace warning message

Video: Find and Replace with Wildcards

In this video, you'll see how to use the Find and Replace command, to clean the data in a contact list. There are written steps, and more examples, on the Excel Find and Replace page.

Video Timeline

  • 00:00 List of Names to Clean Up
  • 01:02 Make a Backup of Data
  • 01:30 Remove Colons and Text
  • 02:30 Remove Hyphens and Text
  • 03:00 Remove OF and Text

5) Remove Spaces - Excel Macro

Instead of using TRIM formulas to remove spaces, you can use a VBA Trim function in an Excel macro, to remove leading spaces, trailing spaces, and extra spaces.

  • Note: There are two Trim functions in Excel VBA, described in the next section

The advantage of using a macro is that this is a quick clean up task, and you don't need to write formulas, or use extra columns on your worksheet.

Code: TrimExtraSpaces_App

To add this Excel VBA macro to your workbook, copy the code shown below. Then, paste the code into a regular code module in your workbook.

To run the macro, follow these steps:

  • Select the cells that you want to remove extra spaces from.
  • Next, on the Excel Ribbon, click the View tab
  • At the right end of the tab, click Macros
  • In the list of macros, click the macro named TrimExtraSpaces_App
  • Click the Run button
  • All normal spaces are removed from the selected cells, except for a single space between words. Remaining cells on the worksheet are not affected.

Tip: You could add a Trim button on the worksheet, to run the macro, if data needs to be cleaned frequently.

Sub TrimExtraSpaces_App()
   Dim rng As Range
   Set rng = Selection
   rng.Value = Application.Trim(rng.Value)
End Sub

Application.Trim vs WorksheetFunction.Trim

In Excel VBA, there are two Trim functions:

  • Application.Trim
  • WorksheetFunction.Trim

Function Similarities

Both functions do the same task -- they remove all spaces from the input, except for a single space between words.

Function Differences

Although the results are the same, here are the differences betwee the two Trim functions:

Application.Trim can accept a string, or a range of cells, as its input, and it returns a variant data type. In the macro shown above, the entire selection had its values trimmed with a single step in the code:

  • rng.Value = Application.Trim(rng.Value)

WorksheetFunction.Trim can only accept a string as its input, and returns a string data type. To use this function, the code needs to loop through all of the cells in the selection, and trim each cell's value separately.

In the macro shown below, the cells are trimmed in a For Each...Next loop

Sub TrimExtraSpaces_Wks()
  Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c In rng
c.Value = _
WorksheetFunction.Trim(c.Value)
Next c End Sub

Get the Sample File

To see the formulas used in today's examples, you can get the Remove Spaces sample workbook.

The file is zipped, and is in xlsm file format, and contains the Trim macros from this page..

More Functions Tutorials

Find and Replace

SUBSTITUTE or REPLACE

 

 

Last updated: June 11, 2023 2:10 PM