Purpose : Removes un-necessary spaces in a given text
Return value : Text with extra spaces removed.
Syntax : =TRIM (text)
Parameter list : text
- The text from which to remove extra space.
Example:
Image 1a
Image 1b
=TRIM(A1) [Formula]
Cell A1 contains the string:
="James"&CHAR(32)&CHAR(32)&"Bond"&CHAR(160)&CHAR(160)&"007".
Note that there are 2 spaces between " James" and "Bond"
and then 2 spaces (nonbreaking space characters with decimal value of 160)
between "Bond" and "007". The string Length - LEN(A1) - is
16 characters. Refer Image 1a.
Formula returns the string "James Bond 007". TRIM
Function deletes one space between "James" and "Bond".
Length of returned string is 15 characters. TRIM function does not remove the
nonbreaking spaces represented by CHAR(160), which can be done by using the
SUBSTITUTE function as shown below. Refer Image 1b.
=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32))) [Formula]
Replaces each nonbreaking space character (Unicode value of 160)
with a space character (ASCII value of 32) by using the SUBSTITUTE function,
and then removes the multiple embedded spaces from the above string in Cell A1
(Image 1a).
Formula returns the string "James Bond 007". Length of
returned string is 14 characters. Refer Image 2.
