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 data of some students, showing IDs and corresponding details with them.

Data set to remove extra spaces in Excel;


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 of TRIM and Other Functions to Remove Extra Spaces

The TRIM function removes all spaces from text except for single spaces between text. When we receive any text from another application that may have irregular spacing, we can use TRIM.

Syntax:

TRIM(text)

Argument:

text -The reference text from where we want to remove spaces.

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.1 Remove All Extra Spaces in an Entire Data Column

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

Step 1:

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

Use of TRIM and Other Functions to Remove Extra Spaces

Step 2:

  • 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

Step 3:

  • Now, press Enter.

Step 4:

  • Drag the Fill Handel icon to the last cell.

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


1.2 Remove Only Leading Spaces in Excel 

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.

Use of TRIM and Other Functions to Remove Extra Spaces

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

Step 1:

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

Step 2:

  • Press the Enter button.

Use of TRIM and Other Functions to Remove Extra Spaces

Step 3:

  • Drag the Fill Handel icon to the last cell.

Use of TRIM and Other Functions to Remove Extra Spaces

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 in Excel

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

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

Use of TRIM and Other Functions to Remove Extra Spaces

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

Step 1:

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

Use of TRIM and Other Functions to Remove Extra Spaces

Step 2:

  • Then, press Enter.

Step 3:

  • Drag the Fill Handel icon to the last cell.

Use of TRIM and Other Functions to Remove Extra Spaces

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 of the Find & Replace Command to Remove Extra Spaces

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

Step 1:

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

Find & Replace Tools to Remove Extra Spaces

Step 2:

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

Find & Replace Tools to Remove Extra Spaces

Step 3:

  • 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.

Step 4:

  • 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

Step 5:

  • Finally, we get the result.


Similar Readings:


3. Excel Power Query to Remove Spaces

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

Step 1:

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

Excel Power Query to Remove Spaces

Step 2:

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

Excel Power Query to Remove Spaces

Step 3:

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

Step 4:

  • The Power Query window will appear now.

We can see different options and commands here.

Excel Power Query to Remove Spaces

Step 5:

  • 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

Step 6:

After selecting Trim, we get the result below.

Excel Power Query to Remove Spaces


4. VBA Macros to Remove Extra Spaces

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

Step 1:

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

VBA Macros to Remove Extra Spaces

Step 2:

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

Step 3:

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

VBA Macros to Remove Extra Spaces

Step 4:

  • 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

Step 5:

  • 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