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: 3. Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE. When you import data into an Excel worksheet, or download from internet, or copy from a website, or in case of multiple users working on the same worksheet, you might often find unnecessary spaces or irregular characters, at times invisible, which were not intended to be part of the actual data. To remove the extra / unwanted spaces and non-printable characters from the data, use the Excel TRIM & CLEAN functions. 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).