How to Remove Extra Spaces in Excel (4 Methods)

MS Excel is a widely used application in our business and works. We use Excel to manage data, process it, and get valuable information from that data. Sometimes we need to process these data to use in another file or sheet. That time we copy data to our desired location. When we do this, sometimes some errors occur. One of those errors is extra spaces with the data. This article will show how to remove extra spaces in Excel with suitable examples and explanations.

We take the following data of some students, showing IDs and corresponding details with them to discuss the upcoming ways.

Data set with extra spaces that need to be removed


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Remove Extra Spaces in Excel

MS Excel has numerous options and techniques to discard extra spaces from the dataset. The TRIM function is particularly designed to remove spaces. In addition to this, we can join functions like MID, FIND, LEN, CLEAN, and SUBSTITUTE to improvise the use of the TRIM function. Moreover, we can use the Find & Replace Command, the Power Query tool, and Excel VBA Macros too.
So without any further talks, here we will dive into the discussion of 4 suitable methods to remove unnecessary spaces in Excel.


1. Use Excel Functions to Remove Extra Spaces

1.1 Remove All Extra Spaces in an Entire Data Column with TRIM Function

In this section, we will simply apply the TRIM fucntion to remove all the extra spaces. Let’s see the following steps.

📌 Steps:

  • First, add a column named Updated Data to show the results.

  • Then click on Cell D5.
  • Type =TRIM and select Cell C5 in the first argument. Close the parenthesis.
    So, the formula becomes:
=TRIM(C5)

Use of TRIM and Other Functions to Remove Extra Spaces

  • Now, press Enter.

  • Drag the Fill Handel icon to the last cell.

We can see that unnecessary spaces are removed; only single spaces exist after each word.

Note:

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from the text. By itself, the TRIM function does not remove the nonbreaking space character.


1.2 Remove Only Leading Spaces with TRIM, MID, FIND & LEN Functions

If our dataset has leading spaces only, we can remove them with this method. We will combine MID, FIND & LEN functions with TRIM here.

Remind the sample dataset. For better demonstration, we have added some leading extra spaces in the dataset. We are gonna keep other spaces, just remove the leading ones.

Now, let’s follow the steps one by one.

📌 Steps:

  • Click on Cell D5.
  • Then type the following formula:
=MID(C5,FIND(MID(TRIM(C5),1,1),C5),LEN(C5))

Use of TRIM and Other Functions to Remove Extra Spaces

  • Press the Enter button.

  • Drag the Fill Handel icon to the last cell.

Here, we removed only leading Spaces. This method is mostly used when working with numeric data.

Formula Breakdown:

The Formula: MID(C5,FIND(MID(TRIM(C5),1,1),C5),LEN(C5))

  • LEN(C5)
    The LEN function returns the length of the string in Cell C5, which is: 32.
    Output: 32.
  • TRIM(C5)
    This simply removes all the extra spaces from Cell C5.
    Output: “Jose, Age:10, Grade:4”
  • MID(TRIM(C5),1,1)
    The MID function returns the character from the output “Jose, Age:10, Grade:4”, specified by the starting position (1) and length (1).
    Output: “J”.
  • FIND(MID(TRIM(C5),1,1),C5)
    The FIND function returns the position of “J” in Cell C5.
    Output: 1.
  • MID(C5,FIND(MID(TRIM(C5),1,1),C5),LEN(C5))
    Finally, the MID function returns the text from Cell C5 starting from position 1 and takes all the characters till position 32 (LEN(C5)).
    Output: “Jose,       Age:10,     Grade:4”.

1.3 Remove Spaces/Non-breaking Spaces/Line Breaks Using CLEAN, SUBSTITUTE & CHAR Functions

In this section, we will show not only spaces but also line breaks and non-breaking spaces. For that we will use CLEAN, SUBSTITUTE, and CHAR functions with the TRIM function.

Let’s assume that our data have line breaks and non-breaking spaces. So, it may look like this:

Now, let’s see the steps below one by one.

📌 Steps:

  • Click on Cell D5 and write the formula:
=TRIM(CLEAN(SUBSTITUTE(C5,CHAR(160),"")))

Remove Spaces, Non-breaking Spaces, Line Breaks Using CLEAN, SUBSTITUTE & CHAR Functions

  • Then, press Enter.

  • Drag the Fill Handel icon to the last cell.

Now, we can see that all irrelevant items are removed from our data.

Formula Breakdown:

The Formula: TRIM(CLEAN(SUBSTITUTE(C5,CHAR(160),””)))

  • CHAR(160)
    The CHAR function returns the character of a given numeric value. Here the numeric value is 160.
    Output: Non-breaking space
  • SUBSTITUTE(C5,CHAR(160),””)
    It substitutes the non-breaking space with nothing from Cell C5.
    Output: “Jose,  Age:   10,   Grade:   4”
  • CLEAN(SUBSTITUTE(C5,CHAR(160),””))
    The CLEAN function removes the line breaks from the previous output.
    Output: “Jose,  Age:   10,   Grade:   4”.
  • TRIM(CLEAN(SUBSTITUTE(C5,CHAR(160),””)))
    Finally, the TRIM function removes all the unnecessary spaces.
    Output: “Jose, Age: 10, Grade: 4”.

Read more: How to Remove Leading Space in Excel


2. Use Find & Replace Command to Remove Extra Spaces

In this section, we will discuss how to use the Find & Replace command to remove all spaces.

📌 Steps:

  • Select the data from where we want to remove the extra spaces.

  • Go to the Home tab.
  • From the Editing command go to the Find & Select tool.
  • Select Replace from the drop-down list.

Use Find & Replace Command to Remove Extra Spaces

  • When we select Replace, we will get a dialog box.
  • Type a blank space in the Find what box.
  • Keep the Replace with box empty.
  • Then, click Replace All.

  • After clicking Replace All, we will get a Pop-Up showing the number of replacements.
  • Click OK on the Pop-Up.
  • Then click Close of the dialog box.

Find & Replace Tools to Remove Extra Spaces

  • Finally, we get the result.


Similar Readings:


3. Utilize Power Query to Remove Spaces

Here, we will use the Power Query tool to remove spaces. Follow the steps below.

📌 Steps:

  • Select the data from where we want to remove spaces.

  • Go to the Home tab.
  • Select From Table/Range from the Data tab.

  • Now, on the Create Table dialog box select My table has headers.
  • Then press OK.

  • The Power Query window will appear now.

We can see different options and commands here.

Excel Power Query to Remove Spaces

  • Now, go to the Details cell and right-click the mouse.
  • From the options select Transform.
  • From Transform, we will get the Trim option.

Excel Power Query to Remove Spaces

After selecting Trim, we get the result below.

Excel Power Query to Remove Spaces


4. Apply a VBA Code to Remove Extra Spaces

In this section, we will apply VBA Macros to remove the spaces.

📌 Steps:

  • This is the data from that we will remove spaces.

VBA Macros to Remove Extra Spaces

  • From the Home, go to the View tab.
  • From the MACRO command select Record Macro.

  • Name the macro as VBA_MACRO.
  • Then press OK.

VBA Macros to Remove Extra Spaces

  • Enter the VBA command module.
  • Type the below code.
Sub VBA_MACRO()
Dim M As Range
For Each M In Selection.Cells
M.Value = Trim(M.Value)
Next M
End Sub

VBA Macros to Remove Extra Spaces

  • Now, press the F5 to run the code.

VBA Macros to Remove Extra Spaces


Conclusion

In this article, we described 4 methods to explain how to remove extra spaces in Excel. I hope this will satisfy your needs. Share any feedback with us, and give your suggestions in the comment box. And please visit our website Exceldemy.com  to explore more.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo