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

How to remove spaces in Excel (7 simple ways)

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 a way to remove spaces in Excel is a crucial skill for every user.

We often can’t see the space characters, but Excel sees them. They can be leading or trailing spaces which are at the start or end of the text string, but we can also find multiple spaces between words. These spaces 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 additional spaces in Excel so our data can be clean and usable.

The example data for this post is:

In the example data, we can see a lot of trailing, leading, and multiple spaces.

Watch the video

Watch the video on YouTube

Remove spaces in Excel – simple methods

We can use simple Excel functionality to remove additional spaces, such as Find and Replace or Flash Fill. These are fast and quick methods but need to be repeated if data changes. Therefore, they are great for one-off activities.

Find and Replace (#1)

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 single spaces, such as a supplier name, this approach won’t work.

If our data contains double spaces that need to be removed, in the Find & Replace dialog box enter the following:

  • Find what: [enter two spaces]
  • Replace with: [enter a single space]

Repeat these steps multiple times until there are no more items to replace.

Flash Fill (#2)

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


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.

Formulas are useful if we are likely to get new data and have to perform the space removal process again. We simply apply the formula to the new data and it recalculates automatically.

TRIM Function to remove excess spaces (#3)

The TRIM function performs two tasks:

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

The TRIM function has a single argument, which is the text from which to remove spaces.

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

TRIM Function to remove spaces

The formula in Cell F3 is


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

Once calculated, if we want, can copy the values in cells F3:F10 and paste the values into cells C3:C10. Alternatively, we could retain the calculation to handle any future data changes.

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 replace spaces (#4)

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 SUBSTITUTE function replaces specific characters in a text string.


SUBSTITUTE(text, old_text, new_text, [instance_num])


  • Text: The text containing the characters to be substituted
  • Old_text: The text to replace
  • New_text: The text to replace the old_text with
  • Instance_num: Optional. Specifies which occurrence of old_text should be replaced. e.g. if 2 then only the second instance of old_text is substituted. If excluded, all instances of old_text are replaced.

Let’s apply SUBSTITUTE to our example data:

SUBSTITUTE to remove all spaces

The formula in Cell F3 is:

=SUBSTITUTE(B3," ","")

SUBSTITUTE is using Cell B3 and replacing all spaces with an empty text string.

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 apply the techniques.

Power Query (#5)

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 are 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)=>

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

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:


After using the function, double spaces are removed. Once the data is clean, load it back into Excel.

NOTE: For a more advanced method to remove spaces in Power Query, check out this post: Remove excess spaces in Power Query

VBA Macro (#6)

If we remove spaces regularly, having a macro saved within our Personal Macro Workbook may be a quick and easy method.

VBA Trim: remove 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

Excel Trim: remove 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

VBA Replace: 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 (#7)

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


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


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



So, there you go, 7 ways to remove spaces 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.

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match 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:

Leave a Reply

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