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:
Contents
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.
- Select the range of cells to be changed (Cells B3-B10 in our example)
- Click Home > Find & Replace (dropdown) > Replace… (Shortcut: Ctrl + H)
- In the Find and Replace dialog box, enter a space into the find what field, and leave the replace with field blank.
- 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:
- Select the range of cells to be filled
- 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.

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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- 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:
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
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. 👍
Excellent post thank you
Thanks Michael 🙂
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.
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.
Thanks, nice overview !
So many possibilities…
would be nice to include your xlsm file
best rgds
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.