Title: Excel Text and String Functions: TRIM & CLEAN
Headings: Excel Functions
Excel Functions
Excel Text and String Functions: TRIM & CLEAN
Excel Text and String Functions:
Removing spaces and nonprinting characters from text: Excel TRIM & CLEAN functions
Related Links:
TRIM Function (Worksheet)
Example:
=TRIM (" James Bond 007 ") [Formula]
CLEAN Function (Worksheet)
Example:
=CLEAN (A1) [Formula]
Use a combination of the TRIM, CLEAN and SUBSTITUTE functions
Example:
=TRIM (A1) [Formula]
=TRIM (SUBSTITUTE (A1, CHAR (160), CHAR (32))) [Formula]
Example:
=TRIM (SUBSTITUTE (A1,"/","")) [Formula]
Example:
=CLEAN (SUBSTITUTE (A1, CHAR (127), CHAR (7))) [Formula]
Example:
=CLEAN (SUBSTITUTE (A1,CHAR (128),CHAR (7))) [Formula]
Find ASCII value of Characters to be replaced
Find Visible Spaces:
Find Visible ASCII Characters:
=CODE (MID (A1,6,1)) [Formula]
Find Invisible spaces / characters:
Find All Spaces & Characters of ASCII value 127 and above, which cannot be removed by TRIM & CLEAN functions:

Content: Leading and trailing blank characters will result in incorrect sorting & filtering of your data; extra spaces and characters will prevent correct working of your formulas (say, when you do a vlookup), cause formatting & calculation errors, and generally impede in manipulating text/data besides appearing visibly incorrect. TRIM Function (Worksheet)
Use the excel TRIM function to remove all spaces from text leaving only single spaces between words. Trim deletes all leading & trailing spaces in a text string, and multiple spaces within words are reduced to a single space. Note that Trim removes the space character with ANSI code 32, but does not remove the non-breaking space character in the Unicode character set with decimal value 160 which is used extensively in web pages (with html entity &nbsp). Syntax: TRIM (text). Example: =TRIM (" James Bond 007 ") [Formula]
Returns the string "James Bond 007". TRIM leaves single space between words and removes leading, trailing & multiple inbetween spaces from text.