- Remove Trailing Spaces In Excel Numbers
- Delete Leading And Trailing Spaces In Excel
- How To Delete Trailing Spaces In Excel Cell
Nearly every computer user in this modern age has spent his or her fair share of time tinkering around with Excel, trying to coax those silly little cells into behaving just as we need them to, but often they never turn out quite as we expect.
Remove Trailing Spaces In Excel Numbers
Today we’ll examine a few simple techniques to ensure that all your text data in Excel (or Google Docs) is clean and tidy by eliminating any and all leading or trailing spaces.
The Trim function
The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces. The TRIM function below removes 2 leading spaces, 3 extra spaces and 2 trailing spaces. Note: the TRIM function does not remove single spaces between words.
The most obvious (and generally efficient) method for removing both leading and trailing space is to use the TRIM() function. As stated in the official documentation, TRIM() “removes all spaces from text except between words.” Unfortunately, in the case of both Excel and Google Docs, that simply isn’t always true, as we can easily illustrate with a bit of code.
- You use the TRIM function in Excel removes extra spaces from text. It deletes all leading, trailing and in-between spaces except for a single space character between words. The syntax of the TRIM function is the easiest one could imagine.
- Original Title: REMOVE TRAILING SPACES IN A CELL WITH A NUMBER. I have a worksheet which was imported from another package which has numbers with trailing spaces.
- Using the Trim function, you have learned how to remove trailing spaces in Excel and leading spaces too. There can be non-breaking spaces in a text as well. To remove them, simply use Substitute Function and replace the non-breaking space with a normal space.
In a new sheet, begin by inserting a single word into the first cell of column A, or cell A1, paying special attention to include a few spaces before and after the word. My example of A1 contains:
Column B, will be our first test column, and the simplest test for our purposes is to check the length of the text in our cells in column A, using the lovely LEN() function.
To display the length of cell A1 (which in my example text is 18 characters long), the contents of cell B1 should look like this:
Nothing fancy, but this will display the total length of our cell from column A.
Delete Leading And Trailing Spaces In Excel
Now we’ll try out TRIM(), so modify cell C1 like so:
The text will be obviously trimmed to the naked eye, but just to verify, modify cell D1 this time to check the length of our TRIMMED value.
This will be noticeably shorter as expected (10 characters in this example).
Non-Breaking Spaces
As it happens, often when importing text into Excel or using copy/paste, we’re inadvertently adding non-breaking space characters to the text, which are not properly handled as expected by the TRIM() function.
To illustrate, add a new row to our test sheet by modifying cell A2:
In Excel, the CHAR() function allows us to output characters based on the numeric identifier of that character. In this case for testing, CHAR(160) is a non-breaking space, so the contents of A2appears to match that of A1, but in fact the spaces on either side for A2 are non-breaking.
Now copy/paste the three test cells B1:D1 and paste them into row 2 starting at B2. We’ll now clearly see the problem with TRIM() – while it worked in row 1 and removed all the normal spaces, it doesn’t do anything in row 2 and thus our ‘TRIMED’ version is still the full 18-length string.
Absolutely Removing the Excess
To fully resolve our issue and ensure we always remove leading and trailing spaces of all kinds, copy all four cells in row 2 down into row 3, so rows 2 and 3 are presently identical.
The trick is to combine TRIM() with a few more functions.
How To Delete Trailing Spaces In Excel Cell
We start by using SUBSTITUTE() to replace all instances of CHAR(160) (non-breaking space) with normal space characters.
We also use the CLEAN() function which attempts to remove characters that simply wouldn’t display on your machine in the first place.
The end result is the following code, which should replace what you have in cell C3:
Voila! We’re now able to remove all leading and trailing spaces in Excel (and Google Docs) no matter what type of space it is. To apply this to other cells in the column, just copy and paste it, changing the cell specification above from A3 to whatever is necessary to get started.