Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps
Jul 28, 2022
In Microsoft Excel, there might be times when you get left with unwanted leading and trailing spaces in your worksheet.
Cleaning up these unwanted characters manually can take a long time to clear a trailing space and remove extra spaces without using the Excel trim function. But what if I told you that you could remove leading or trailing spaces in Excel quickly and easily?
That's right; excess spaces are gone in the blink of an eye! Leading spaces appear at the start of your text string, and trailing spaces at the end of your text data.
They often get in the way of what you try to achieve in Microsoft Excel. What extra spaces you come up against, leading and trailing, can be removed in several ways.
This article will discuss how you can remove spaces in Excel and never have extra spaces or line breaks get in your way again. 
TRIM Function to Remove Extra Spaces
The TRIM function is an excellent way to remove trailing spaces in your text, apart from single spaces between words.
Here is what the TRIM function formula is to remove spaces: =TRIM(text)
Let's break that down in more detail: The =TRIM aspect highlights the text from which you want the extra spaces removed. The TRIM function makes removing leading and trail spaces rapid and easy. 
However, the TRIM function is not the best option for removing non-breaking spaces in Excel.
Non Breaking spaces are sometimes included in data downloaded as the text for formatting purposes, as it stops an automatic line break from occurring.
We must use a different function to remove leading spaces for non-breaking spaces in Excel. 
Substitute Function to Remove Non-Breaking Spaces
We will need to use the SUBSTITUTE function to remove extra non-breaking spaces. Here's how to replace non-breaking spaces with normal spaces:
- Input the SUBSTITUTE formula in your chosen cell
=SUBSTITUTE( 
- Input your first argument, which is the cell that contains your text that needs to be substituted.
=SUBSTITUTE(XX
- Input the second argument, the existing text you want to substitute.
=SUBSTITUTE(xx,CHAR(xxx),
- Input the third argument, which is the new text you want to replace it with.
=SUBSTITUTE(XX,CHAR(xxx),"")
- Now you have removed each non-breaking space and replaced all the extra spaces with shared spaces, we can use the TRIM function.
Find & Replace to Remove All Spaces
Those options work great for removing only a single space or a couple of consecutive spaces, but what about when you want to remove all the spaces in Excel? This is where the Find and Replace to Remove every space character in your worksheet is.
- Choose the cell range from which you want to remove all spaces.
- Head to Home, Find & Select, Replace, which will open the Find & Replace dialog box.
- Hit the space bar in the Find section to insert a space and leave the Replace with field blank. That way, all space characters will be automatically highlighted.
- Select the Replace All button and hit OK, and you will remove spaces from the selected area.
Removing Leading and Trailing Spaces: Summary and Key Takeaways
You can remove a leading space or extra spaces in Excel! You can even replace extra non-breaking spaces with shared spaces in Excel! Remember to practice using your TRIM function and SUBSTITUTE function to improve your skillset, and for removing spaces across your entire worksheet, don't forget to use the Find & Replace feature.
Related Articles:
Excel Pivot Table Training: Everything You Need to Know
What is a Sunburst Chart and When to Use a Sunburst Chart in Excel
How to Calculate Confidence Interval in Excel: Easy-to-Follow Steps
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
