7 ways to remove additional spaces in Excel

The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding and removing additional spaces is a crucial skill for every Excel user.

We often can’t see the space characters, but Excel sees them. This can cause values not to match and calculation errors to occur, which for an Excel user is almost the worst thing that can happen.

In this post, we will discover how to find and remove unwanted spaces so our data can be clean and usable.

The example data we will be using:

Simple worksheet methods

We can use simple Excel functionality to remove additional spaces, such as Find and Replace or Flash Fill.

Find and Replace

Find and replace may seem obvious, but you would be surprised about the number of people who don’t think about it.

If our data contains leading, trailing, or mid-spaces that all need to be removed, the Find and Replace method is perfect.

In our example data, we have an issue with the Part Number column; it contains lots of rogue spaces.

1. Select the range of cells to be changed (Cells B3-B10 in our example)
2. Click Home > Find & Replace (dropdown) > Replace… (Shortcut: Ctrl + H)
3. In the Find and Replace dialog box, enter a space into the find what field, and leave the replace with field blank.
4. Click replace all
   

Ta-dah! Simple.

Obviously, if our data should contain spaces, such as a supplier name, this approach won’t work.

Flash Fill

Flash Fill was introduced in Excel 2013. It is easy to use and ideal for finding patterns in text strings. It’s a feature that runs in the background and makes recommendations at suitable points. So, you may have used Flash Fill before without knowing it.

With our data on the left, we can type how the first value should display. Then, when we start typing a second value, hopefully, Flash Fill will spring into action and make suggestions.

If you are happy with the recommendations, press the Tab key to accept them. However, in our example, you will notice the period between Spares and com has been removed. We need to keep adding more examples for Flash Fill to use.

Once we have entered more values:

1. Select the range of cells to be filled
2. Click Data > Flash Fill
   

Done! Because we rely on Excel’s algorithm, we should check the other values to ensure they are right. In the screenshot below, column F shows the clean data.

Flash Fill is a pretty nice feature.

Note: If Excel cannot find a matching pattern, it will return an error message.

Functions

If we are looking for a more dynamic method of removing unnecessary spaces, then functions such as TRIM or SUBSTITUTE may be a better solution.

TRIM Function to remove excess spaces

The TRIM function performs two tasks:

• Removes leading and trailing spaces
• Reduces multiple spaces to a single space

Let’s head back and see how we can use this with our Supplier name.

The formula in Cell F3 is

=TRIM(C3)

The result is that the spaces have now been removed. It’s a simple and powerful function.

Note: The VBA and Power Query versions of trim are different. They do not remove additional spaces from the middle of strings.

SUBSTITUTE Function to remove all spaces

If you want to combine the result of Find & Replace with the benefit of a function, then SUBSTITUTE is the way to go for you.

The formula in Cell F3 is:

=SUBSTITUTE(B3," ","")

SUBSTITUTE is using Cell B3 and replacing all spaces with blank characters.

Advanced methods

Depending on your specific scenario and experience, you may be using more advanced Excel features, such as Power Query, VBA Macros, or Office Scripts.

As these are more advanced features, we will not go through these step-by-step. If you’re using these already, you can apply your existing knowledge to use the following techniques.

Power Query

Power Query is Excel’s tool for cleaning dirty data. However, it is as easy as using the methods above.

Trim the leading and trailing spaces

To remove the leading and trailing spaces, select the column to be cleaned and click Transform > Format (dropdown) > Trim from the Ribbon.

The leading and trailing spaces will be removed instantly.

Remove double spaces within text

Unfortunately, the Power Query version of trim does not remove double spaces inside a text string. So, if this is your scenario, we need to turn to a more advanced technique.

The following custom function can be used to remove spaces. Enter the function into a blank query using the Advanced Editor.

(textValue as text)=>

let
    SplitText = Text.Split(textValue," "),
    ListNonBlankValues = List.Select(SplitText,each _<> ""),
    TextJoinList = Text.Combine(ListNonBlankValues," ")
in
    TextJoinList

I have named this query fxTrim.

To apply this function, click Add Column > Custom Column

In the Custom Column window, we can use the custom function.

The formula used above is:

=fxTrim([Supplier])

The spaces should now be removed. Once the data is clean, load it back into Excel.

VBA Macro

If we remove spaces regularly, having a macro saved within our Personal Macro Workbook may be the easiest option.

Trim leading & trailing spaces

The following code removes leading & trailing spaces, but not double spaces from within a text string.

Sub RemoveLeadingTrailingSpaces()

Dim c As Range

'Loop through each cell in selection
For Each c In Selection

    'Apply VBA Trim function to each cell
    c = Trim(c)

Next c

End Sub

Trim leading, trailing, and double spaces

The following code will replicate Excel’s TRIM function.

Sub RemoveAdditionalSpaces()

Dim c As Range

'Loop through each cell in selection
For Each c In Selection

    'Apply Excel TRIM function to each cell
    c = Application.WorksheetFunction.Trim(c)

Next c

End Sub

Remove all spaces

The following code removes all spaces.

Sub RemoveAllSpaces()

Dim c As Range

'Loop through each cell in selection
For Each c In Selection

    'Apply VBA Replace function to remove spaces
    c = Replace(c, " ", "")

Next c

End Sub

Office Scripts

If you are using Excel Online and have Office Scripts available, then you can use the following scripts:

Trim leading & trailing spaces

The following script removes leading & trailing spaces.

function main(workbook: ExcelScript.Workbook) {

//Declare and assign variable for the selected cells
let rng = workbook.getSelectedRange();

//Create array for the values in the range
let arr = rng.getValues();

//Loop through each item in the 2D array
for (let i = 0; i < arr.length; i++) {
  for (let j = 0; j < arr[0].length; j++) {

    //Apply JavaScript Trim function to each cell
    arr[i][j] = arr[i][j].toString().trim();

  }
}

//Push array back to cells using the setValues method
rng.setValues(arr);

};

Trim leading, trailing, and double spaces

The following script replicates the outcome of Excel’s TRIM function.

function main(workbook: ExcelScript.Workbook) {

//Declare and assign variable for the selected cells
let rng = workbook.getSelectedRange();

//Create array for the values in the range
let arr = rng.getValues();

//Loop through each item in the 2D array
for (let i = 0; i < arr.length; i++) {
  for (let j = 0; j < arr[0].length; j++) {

    //Apply RegEx to remove additional spaces function to each cell
    arr[i][j] = arr[i][j].toString().replace(/\s+/g, ' ').trim();

  }
}

//Push array back to cells using the setValues method
rng.setValues(arr);

};

Remove all spaces

The following script removes all spaces.

function main(workbook: ExcelScript.Workbook) {

//Declare and assign variable for the selected cells
let rng = workbook.getSelectedRange();

//Create array for the values in the range
let arr = rng.getValues();

//Loop through each item in the 2D array
for (let i = 0; i < arr.length; i++) {
  for (let j = 0; j < arr[0].length; j++) {

    //Apply JavaScript Replace function to remove all spaces
    arr[i][j] = arr[i][j].toString().replace(/\s/g,'');

  }
}

//Push array back to cells using the setValues method
rng.setValues(arr);

};

Conclusion

So, there you go, 7 ways to remove additional spaces from your data in excel.

Many would say that we only need one way to achieve this; however, context is everything. Why use a Power Query method if Find and Replace will work? But if you’re using Power Query to extract data, then the Find and Replace method would be useless. So, the choice is entirely dependent on your situation.

Hopefully, this post has provided you with enough ways to remove spaces in Excel, no matter your scenario.