This function removes all spaces from text except for single spaces between words. One can use TRIM on text that you have received from another application that may have irregular spacing or on text that may have been typed irregularly.
Syntax: TRIM (text)
- Text : is the text from which you want spaces removed.
Remarks
- The text argument is compulsory.
- If you specify a number instead of a text value as this parameter, the function would return an error which is-#VALUE.
Example
The TRIM function can be used in any database where you have stored any text values and you want to delete the spaces from the beginning and the end of the text value. The function extracts the text value from the characters in the text parameter however it leaves out the spaces from the text.
This function returns this text value as its result.
=TRIM (" Sale Price ") |
-returns ("Sale Price") |
=TRIM (" Switzerland") |
-returns ("Switzerland") |
=TRIM ("Power OLAP") |
-returns ("PowerOLAP") |
*Spaces between words are not deleted or ignored. Only the spaces in the beginning of a word or at the end of the word are deleted.
Note: A number supplied within double quotes is taken as a string parameter.
Let us take the example of a database in PowerOLAP where we have certain text values for name, last name, city, country etc., and we need to manipulate these text values and arrive at results with different permutations and combinations. We shall use this example in this case to find a value which trims the characters specified as text and trims these to not include the spaces at the beginning of the text value and at the end of the text value. The text value here is "Input" to this cube, in this instance it is "Continent" member. Here we have written a formula in the appropriate cube in PowerOLAP, pulling in the value from the above mentioned parameters and then deriving the trimmed value from the supplied parameter. A text vale is returned as the result for the TRIM function.
.png)
The formula above writes values into the "StringDataFunctions" cube to the "String Data Manipulations" dimension into the member named "TRIM" by creating the appropriate value for this member, based upon the parameters that are given as input to this function, namely the reference for the "Continent". The result is returned by trimming the values that are given as the input parameter. The extracted text value is the text returned by the function.