Excel TRIM function for removing spaces
Whether text is based on external sources or written within Excel, you may remove the spaces on left and right of the text by using the TRIM function.
In Excel, the text may contain unwanted leading or trailing spaces especially if the text is obtained from outer sources like web pages. Whether the text is based on external sources or written within Excel, you may remove the spaces not only on left and right of the text but within the words by using the TRIM function. For example,
“ The TRIM Function ”
After using TRIM()
“The TRIM Function”
Not only spaces from left and right but more than one spaces within the words the in string will also be removed by the TRIM function.
In this tutorial, I will show you examples of trim for removing spaces from both sides, within text of the text string along with only removing spaces from the left of the text.
The Syntax of TRIM function
The general syntax of TRIM function is:
TRIM(text)
You may refer the cell or use text as constant literal.
The example of removing spaces by TRIM
In this example, the text is used in the TRIM function that contains leading and trailing spaces (spaces towards the left and right of the string). The formula for removing spaces:
The result of TRIM function:
You can see the difference how TRIM function deleted the spaces from left and right of the given text.
Referring Cell text in TRIM function example
The following example shows using the cell reference in the TRIM formula. In the A3 cell, I entered the following text:
“ Hang on I see you . “
The spaces are given in right and left sides and also between the words in the text. See how TRIM function removed these spaces:
=TRIM(A3)
You can see, after using the TRIM function, the spaces from left and right sides are removed and extra spaces are removed from the words.
An example of removing leading spaces
In certain situations, you may only require removing the leading spaces (the spaces towards left side). The Excel has no direct function like LTRIM or RTRIM to remove spaces from left or right only, respectively.
However, you may use different formula combinations to achieve that. In the following example, the TRIM function is used with LEFT, FIND and REPLACE functions to remove leading spaces.
The formula:
You can see, as I entered the following text in the A3 cell:
“ Left Trim only . Done “
It removed only spaces from the left side. The resultant string is:
“Left Trim only . Done “
Trimming the number columns
The columns that contain numbers may also contain unwanted space. You may use the TRIM function for removing spaces for number column just like the text. However, be careful, as simply using TRIM makes number as text.
You may notice the numbers aligned left after using TRIM whereas the default behavior is aligning numbers right.
See an example below:
You can see numbers are displayed left aligned while SUM of B2 to B6 is 0.
The issue can be sorted by using VALUE function in conjunction with TRIM function as shown below:
You can see, the A column contains raw numbers (without any formula with spaces). The B column displayed number after using TRIM function only. The sum is displayed as 0.
The C column displays numbers by using this formula:
=VALUE(TRIM(A2))
It goes from C2 to C6 and we got the sum that is correct along with alignment.
An example of TRIM with non ASCII space
As mentioned earlier, the TRIM function is designed to remove spaces for ASCII character (32). If you try removing spaces with non-ASCII spaces then it will not. This generally happens due to non-breaking space i.e. CHAR(160) that results if you copy text from the webpages.
To demonstrate that, I have copied a text line from a web page that contained leading, trailing and extra spaces within words. The web page spaces are given by “ ”. See the text and output as I applied TRIM formula for trimming that text:
You can see, even after using the TRIM function, no spaces are removed. This is how the web page text is created and copied from the browser:
1 hello and welcome
How to sort this issue for removing spaces?
You may use the SUBSTITUTE function that will first replace the non-breaking space character CHAR(160) to ASCII space character and then TRIM function will remove those spaces. See the formula and output with comparison:
The SUBSTITUTE/TRIM formula:
The result:
You can see the spaces are removed from the same web page text.
