When copying data into Excel from other sources – be it web pages, documents, or other Excel documents sometimes you end up with unwanted formatting, such as numbers with invisible spaces that prevents it as being read as a number or converted into one. Or just ending up with data you don’t want.
The TRIM function in Excel is useful for removing trailing spaces after text that serve no purpose.
The more annoying issue that I’ve come across is a blank space that looks like one but isn’t. It is usually character # 160 which looks just like a blank space, only thinner than normal. If you enter char(160) in a cell you’ll see what this looks like. It can be a frustrating process because this character you can’t get rid of by just searching for blank characters and doing a find and replace or the trim function. It’s possible to see this with other characters as 160 isn’t the only one that looks like a blank space, but it is the only one I’ve come across so far.
The solution is to replace character 160 with character 32 (this is a normal space that will get eliminated with the TRIM function). To do this, use the following formula, assuming cell a1 is the cell that needs the cleanup:
=TRIM(SUBSTITUTE (A1,CHAR(160),CHAR(32)))
You can add a *1 to the end of the formula to convert it to a number if necessary.
See below for an example. The only difference between the cells in A2 and A3 is A2 has a normal blank space after it whereas cell A3 has character 160 after it. Both cells equal the same length (12 characters) as show in column B. column C is what the cells look after using the TRIM function – they will look the same but column D recalculates the length and the top cell has now gone down by one character (the trailing space). Column E is after the above formula is used. You can see now the updated length in column F is 11 for both, meaning the trailing character has been deleted in both cells.
As a side note, if you’re need to reference a specific character in excel you’ll notice all it takes is using the CHAR formula. If you’re not sure which number of the character you need, you can have the CHAR function reference the numbers 1 to 255 and you’ll see all the different characters available.
Add a Comment
You must be logged in to post a comment