This Excel tutorial explains the use of TRIM function to get rid of unnecessary spaces. You will learn a quick and easy way to remove leading, trailing and extra spaces between the words.
The Excel TRIM function removes all extra spaces from a text. Whether there are leading, trailing, multiple or extra spaces in the text, TRIM removes all leaving one single space between the words.
The use of TRIM function can arise when there are extra spaces in data and you have to compare multiple columns/rows to find duplicates or while performing VLOOKUP function which returns #N/A! error even when both data-set contains the lookup values or in situation when received raw data needs to be cleaned up before being used for creating reports.
Although there are a few different ways to get rid of redundant spaces, TRIM is one of the fastest resource in Excel to remove spaces from text.
TRIM ( text )
text – The only argument that specifies the text from which to remove extra and unwanted spaces.
Example: – TRIM Function
Let’s explore a few quick live examples to know about TRIM function and its’ uses.
How to remove unwanted spaces in an entire column
Let’s assume there is a column containing names which have some extra whitespaces in the list and now you have to remove all redundant spaces. You can get rid all of them in one go using TRIM function.
- Start with jumping on to cell where you want to write function to get answer and enter TRIM function as follow. (B2 in our case)
- Drag the function to copy the function down to the last active cell
As you can see, TRIM has removed all extra spaces whether they were in beginning, middle or at the end of the text and returned with only space between words.
How to remove extra spaces and non-printable character in a column
As you have seen in the previous example that Excel TRIM function easily removes the extra spaces by leaving only a space between the words. But what if your data contains some special characters (non-printable) and extra spaces? Will TRIM function work in such situations too?
Look at the above image. Have you noticed something? Some Special characters which are non-printable characters, are still there in text. The Excel TRIM is ideally used to remove the ASCII space character (code 32 in the 7-bit ASCII system), known as whitespace or space.
The non-printable characters (first 32 non-printing characters in 7-bit ASCII code system) can be cleaned using Excel CLEAN function and to remove extra spaces and non-printable characters, TRIM can be combined with CLEAN function.
Let’s clean the data given in column ‘A’ using the formula is as follows:
The above formula removes the non-printable characters as well as unwanted spaces and data looks clean and good to be used further. In this case, first CLEAN function executed and supplied the text after cleaning non-printable characters to TRIM and further TRIM returned after removing unwanted spaces in the text.
TRIM function is unable to remove spaces in my Excel
By now you must have understood that TRIM is designed to remove only ASCII Space, 32nd character in the ASCII character set but there is one more space character which looks similarly as ASCII space but known as non-breaking space. It is a Unicode character and represented by ASCII decimal value 160. It appears on web pages as HTML entity and cannot be removed by TRIM function.
Suppose you export data in Excel from third part application and you see bunch of white spaces in data. You use TRIM function in Excel to remove extra spaces but realize that some text entries have leading spaces post using TRIM function too.
So when you see there are still some whitespaces left after using TRIM function also, you can use SUBSTITUTE function to replace non-breaking space with regular space and have the TRIM function to remove extra spaces.
Write the exact formula in cell B2 as follow:
=TRIM(SUBSTITUTE(A2, CHAR(160), “”))
How this formula is working?
Let’s break each argument of above formula and understand how this formula is working.
- CHAR function returns the ASCII character of code value 160 as non-breaking space to the second argument of SUBSTITUTE function.
- Use CODE function to know the code value of the whitespace left after trimming text. Non-breaking space is represented by 160 and CODE function returns the same.
- SUBSTITUTE is replacing the non-breaking space into regular space and passing the new text to the TRIM.
- TRIM finally removes the irrelevant spaces in the text.
Tip: If your data contains spaces other than ASCII space (32) and Unicode character (160), CODE function can be really useful to know the code of the same which further can be replaced with regular spaces using SUBSTITUTE and later TRIM to remove irrelevant spaces.
Additional Notes: –
- Use TRIM to remove extra spaces before comparing data in columns/rows/cells.
- TRIM does not removes non-printable characters.
- TRIM combining with CLEAN function removes extra spaces, special characters, line breaks etc.
- USE CLEAN function to remove non-printable characters from text.
- If TRIM does not work, use CODE function to know the code of the character that looks like space and then use either SUBSTITUTE or CLEAN function as per need.
- Function leaves single space between words after removing extra and unwanted spaces
- Use TRIM with VLOOKUP to remove unwanted spaces from lookup value
- Unicode character ‘No-Break Space’ (160) used on web pages as the HTML entity will not be removed only using TRIM function.
- SUBSTITUTE function can be used to replace Unicode character ‘No-Break Space’ (160) in normal space and later TRIM takes care of extra spaces
CLEAN | CODE | SUBSTITUTE | UPPER | LOWER | PROPER