Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

7 ways to remove additional spaces in Excel

Remove spaces in Excel - Featured Image

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
    Find and replace spaces

Ta-dah! Simple.

Part Numbers corrected with Find and Replace

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.

Flash Fill running automatically when entering data

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
    Data Flash Fill from Ribbon

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.

Clean Data after Flish Fill

Flash Fill is a pretty nice feature.

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

Flash Fill Error

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.

TRIM Function to remove spaces

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.

SUBSTITUTE to remove all spaces

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.

Transform Format Trim

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.

Apply custom function to remove spaces

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.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

8 thoughts on “7 ways to remove additional spaces in Excel

  1. Marc says:

    The only thing I would add to this is that when all the spaces are gone and you still have data that’s “not working” as expected (dates that don’t sort, numbers that won’t format, etc…) and you absolutely can’t figure out why Excel is treating it differently, you’ve deleted and repasted, deleted and retyped, used the nuclear version of Clear on the cell and retyped, and your data still won’t play ball, a fixed width text-to-columns performed on the column containing the problem values will save your day

    • Excel Off The Grid says:

      I’ve not experienced those issues, so I really appreciate you sharing your experience here. I’m sure it will help others who are stuck. 👍

  2. Bill says:

    Suggestion: To remove double spaces, use your find/replace technique. In the Find box, enter two spaces. In the Replace box, enter one space. You should repeat the find/replace until it says that no more were found.

    • Excel Off The Grid says:

      Thank Bill.

      Yes, that is a valid technique. The same is true of the Power Query technique. I did not include it as once we start down that route of repeating actions, I think other options are better.

    • Excel Off The Grid says:

      Hi Danny – With Microsoft now blocking .xlsm files downloaded from the internet by default. I am considering removing all .xlsm downloads from the site. As I don’t want to be part of something that could raise security concerns for users.

Leave a Reply

Your email address will not be published. Required fields are marked *