Trailing spaces can be a pain, we get it. These spaces can cause issues with your calculations, filtering, or other operations.
But, have no fear,
There are 4 great ways to remove trailing spaces in Excel, including functions like TRIM, CLEAN, and SUBSTITUTE, or even a combination of functions to effectively remove the trailing spaces from your data.
In this article, we’ll cover various methods to handle trailing spaces in Excel, including:
Identifying and removing spaces manually
Using the TRIM, CLEAN, and SUBSTITUTE functions to remove trailing spaces
Automating the process with Power Query
By the end of this article, you’ll have a better understanding of how to deal with trailing spaces in Excel, ensuring your data remains accurate and clean.
Let’s get started!
How to Identify Trailing Spaces in Excel
Before we delve into how to remove trailing spaces in Excel, it’s important to understand how to identify them.
There are a few ways to determine if your data contains trailing spaces:
Visual inspection:
Look closely at the cells in your data set, especially the ones that look longer than they should be. Unlike leading spaces, trailing spaces can be hard to spot, so it’s essential to be thorough.
Using the LEN function:
The LEN function in Excel returns the number of characters in a cell, including spaces.
You can use this function to compare the length of the original text with the length of the text after removing spaces. If there is a difference, it’s likely that there are trailing spaces.
=LEN(A2)-LEN(TRIM(A2))
Applying conditional formatting:
You can apply conditional formatting to highlight cells with trailing spaces. To do this, select the cells, go to the Home tab, click on Conditional Formatting, and choose New Rule. Then, select “Use a formula to determine which cells to format,” enter the below formula in the given box and click the Format button to select formatting.
=ISNUMBER(SEARCH(” “,A2))
It’s important to note that while the methods mentioned above can help you identify trailing spaces, you still need to remove them to ensure the accuracy of your data.
In the following sections, we’ll show you several methods to remove trailing spaces in Excel.
1. How to Remove Trailing Spaces Manually: 4 Simple Steps
One of the simplest ways to remove trailing spaces in Excel is by doing it manually. Here are the steps to remove spaces manually:
Step 1
Select the cells: First, select the cells or range of cells that you want to remove the trailing spaces from.
Step 2
Access the formula bar: Click on the cell in the formula bar, which is located above the worksheet grid, and displays the text or formula of the active cell. This is the place where you can directly edit the cell’s content.
Step 3
Move the cursor to the end of the text: Click on the cell in the formula bar and move the cursor to the end of the text. You can also press the “End” key to quickly navigate to the end of the text in the formula bar.
Step 4
Backspace or delete: Once the cursor is at the end of the text, press the backspace or delete key to remove the trailing spaces.
Repeat the above steps for each cell in the selected range.
Remember that manual removal of spaces is suitable for small datasets. For larger datasets, it’s best to use Excel functions or other tools to automate the process.
In the following sections, we’ll show you how to remove trailing spaces using functions like TRIM and SUBSTITUTE.
2. Using TRIM to Remove Trailing Spaces in Excel
The TRIM function in Excel is a powerful tool for removing extra spaces, including leading, trailing, and multiple spaces between words.
To remove trailing spaces using the TRIM function, follow the steps below:
Select a blank cell where you want the cleaned text to appear.
Enter the following formula:
=TRIM(cell reference)
Replace cell reference with the cell containing the text that you want to remove trailing spaces from.
The TRIM function will remove any extra spaces from the text in the specified cell, including trailing spaces.
It will not remove normal spaces in Excel and leaves only a single space between words.
In the following example, we have some text in cell A1 that has extra spaces. We used the TRIM function to remove all the spaces that are not required and display the cleaned text in cell A2.
Note that the original text in cell A1 is not modified. The TRIM function only returns the cleaned text without changing the original text.
3. Using SUBSTITUTE to Remove Trailing Spaces in Excel
The SUBSTITUTE function in Excel is a versatile tool for replacing text. To remove trailing spaces using the SUBSTITUTE function, follow the steps below:
Select a blank cell where you want the cleaned text to appear.
Enter the following formula:
=SUBSTITUTE(cell reference, ” “, “”)
Replace cell reference with the cell containing the text that you want to remove trailing spaces from.
The SUBSTITUTE function will remove any extra spaces from the text in the specified cell, including trailing spaces.
In the following example, we have a number in cell A2 that has trailing spaces. We used the SUBSTITUTE function to remove the trailing spaces and display the cleaned number in cell A3.
This method works best for removing a single trailing space from each cell.
If you need to remove multiple trailing spaces, you can use the following formula:
=TRIM(SUBSTITUTE(A2,” “,REPT(” “, 100)))
This formula combines the TRIM, SUBSTITUTE, and REPT functions to remove all trailing spaces from the text in the specified cell.
4. Using a Combination of Functions to Remove Trailing Spaces
You can also use a combination of functions like TRIM, CLEAN, and SUBSTITUTE to remove trailing spaces from your data. This approach can be particularly useful when dealing with complex or mixed characters that may not be handled by a single function.
The CLEAN function helps you to remove non printing characters such as line breaks, carriage return etc.
Let’s take a look at an example using the following formula:
=TRIM(SUBSTITUTE(CLEAN(cell reference),CHAR(160),””))
This formula uses the CLEAN function to remove non-printable characters, the SUBSTITUTE function to replace multiple spaces with a single space, and the TRIM function to remove leading and trailing spaces. It also includes the CHAR(160) function to handle non-breaking spaces.
In the following example, we have some text in cell A1 that has trailing spaces, non-printable characters, and non-breaking spaces. We used the combination of TRIM, SUBSTITUTE, CLEAN, and CHAR(160) functions to remove the trailing spaces and display the cleaned text in cell B1.
As you can see, the cleaned text in cell B1 is free of trailing spaces, non-printable characters, and non-breaking spaces.
Using Power Query to Remove Trailing Spaces
If you have Microsoft 365 or Excel 2010 and later, you can use the Power Query Editor to remove trailing spaces in your data.
For those of you who haven’t used it, Power Query is a powerful data transformation tool that allows you to connect, transform, and load data from various sources into your Excel workbook.
To remove trailing spaces using Power Query, follow these steps:
Step 1
Select the data table.
Step 2
Go to the Data tab in Excel and click on “From Table/Range” in the Get & Transform Data section.
Step 3
In the Power Query Editor, select the column or columns that you want to clean.
Step 4
Go to the Transform tab and click on “Trim” in the Format Drop down of the Text Column section.
Step 5
Click on “Close & Load” to apply the changes and load the cleaned data back into your Excel worksheet.
The Power Query Editor will automatically remove trailing spaces from the selected column(s) and load the cleaned data into a new worksheet.
This method is especially useful for handling large datasets, as it allows you to clean your data in a more efficient and scalable manner.
In this article, you learned how to remove trailing spaces in your Microsoft Excel sheet using different functions, manual methods, and tools like Power Query. You also learned how to identify and deal with trailing spaces in your data, ensuring its accuracy and cleanliness.
By using the techniques outlined in this article, you can confidently work with data in Excel, knowing that you have the knowledge and tools to handle trailing spaces effectively.
Final Thoughts
As you can see, removing trailing spaces in Excel is a straightforward process. By using the methods we’ve discussed, you can ensure that your data is clean, consistent, and ready for analysis.
Whether you choose to use the TRIM function, Find and Replace, the CLEAN function, or VBA code, each method has its own advantages and can be applied depending on your specific needs.
Additionally, understanding the importance of removing trailing spaces can save you time and headaches in the long run. Clean data not only looks better but also reduces the risk of errors in your calculations and data analysis.
So, next time you’re working with Excel, don’t let those sneaky spaces go unnoticed. Use the tools and methods at your disposal to remove them and keep your data clean and reliable.
Interested in exploring the world of analytics tools? Check out the video below:
Frequently Asked Questions
How can I remove spaces at the end of a text string in Excel?
To remove spaces at the end of a text string in Excel, you can use the TRIM function. For example, if your text string is in cell A1, you can use the formula =TRIM(A1) to remove both leading and trailing spaces.
Alternatively, you can use the SUBSTITUTE function as follows: =SUBSTITUTE(A1, ” “, “”) which will only remove trailing spaces.
What is the formula to remove spaces after text in Excel?
To remove spaces after text in Excel, you can use the TRIM function. For example, if your text is in cell A1, you can use the formula =TRIM(A1).
How do I remove unwanted spaces in Excel?
To remove unwanted spaces in Excel, you can use the TRIM function. For example, if you have a text string in cell A1 that contains unwanted spaces, you can use the formula =TRIM(A1) to remove those spaces.
How can I trim spaces in a cell?
To trim spaces in a cell, you can use the TRIM function. For example, if you want to trim spaces in cell A1, you can use the formula =TRIM(A1).
Is there a method to delete spaces at the end of a cell?
To delete spaces at the end of a cell, you can use the TRIM function. The TRIM function will remove both leading and trailing spaces.
Alternatively, you can use the SUBSTITUTE function as follows: =SUBSTITUTE(A1, ” “, “”) which will only remove trailing spaces.
