How to use Excel TRIM function

0
234

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.

TRIM Function

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.

Uses:-

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.

Remove Leading Spaces in Excel

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.

Syntax:

TRIM ( text )

Arguments:

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)

=TRIM(A2) 

Excel TRIM Function

  • 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.

Tip: If you want to replace the original values with trimmed names, you can use Paste Special > Values feature.

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?

Remove non printable character - Excel

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:

= TRIM(CLEAN(A2))

Trim Function in Excel

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.

Trim function not working - Excel

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.

Excel TipsTip: 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

Related Articles:-

CLEAN | CODE | SUBSTITUTE | UPPER | LOWER | PROPER

LEAVE A REPLY

Please enter your comment!
Please enter your name here