When you copy or import text data into an Excel worksheet, sometimes the worksheet retains extra spaces next to the pasted content. Usually, the TRIM() function can only remove these unwanted spaces, whether they are between words or at the beginning or end of a string. However, in certain situations TRIM() cannot do the job.
On a computer, a space between words is not a space, but a character – and there is more than one type of space. One of the space characters commonly used in web pages that TRIM() does not remove is non-breaking space †
If you have imported or copied data from web pages, you cannot remove extra spaces with the TRIM() function if they are created by non-breaking spaces.
- Fixed and regular spaces
- Remove non-breaking spaces
- How the formula works
Fixed and regular spaces
Spaces are characters and each character is referenced by its ASCII code value. ASCII stands for US Standard Information Exchange Code is an international standard for text characters in computer operating systems that creates one set of codes for 255 different characters and symbols used in computer programs.
ASCII code for non-breaking space − 160 † ASCII code for normal spaces – 32 †
The TRIM() function can only remove spaces with an ASCII code of 32.
Different types support different sets of ASCII codes. The default table offers 127 possible values; headsets must support at least a 127-character ASCII card to be considered valid. But the “extended” ASCII characters called complement codes often add extra characters to your favorite fonts. In fact, the non-breaking space itself is an extended ASCII character, while the standard space… well, default.
Remove non-breaking spaces
Remove non-breaking spaces from a string using the TRIM(), SUBSTITUTE(), and CHAR() functions.
Because the SUBSTITUTE() and CHAR() functions are nested in the TRIM() function, the formula must be entered in a table rather than using function dialog boxes to enter arguments.
The formula, assuming the non-breaking data is in cell A1, is:
= TRIM (ПОДСТАВИТЬ (А1, СИМ (160), CHAR (32)))
How the formula works
Each nested function performs a specific task:
The CHAR function provides the corresponding ASCII codes for two different spaces in the formula – 160 and 32
The SUBSTITUTE function replaces or replaces all non-breaking spaces between words with regular spaces
The TRIM function removes extra regular spaces between words so that the operator appears normally on the sheet
Given the order of the editing logic of Excel formulas, the formula calls the SUBSTITUTE() function and tells it to replace each instance of CHAR(160) – a non-breaking space – with the standard CHAR (32) space in cell A1. The TRIM() function then removes default spaces from the replaced string.
If TRIM() can’t do the job, you may have issues other than non-breaking spaces, especially if you’re working with source material rendered in HTML. When you paste things into Excel, paste it as plain text to remove the background formatting from the string and remove special formatting, such as characters appearing as white on white – what looks like like a space, but no. Also check for built-in tabs, which can be replaced using the same formula as above, but replacing the ASCII code 160 with 9.
SUBSTITUTE() is useful for replacing one ASCII code with another.