The CLEAN function accepts a text string and returns text that has been "cleaned" of line breaks and other non-printable characters. You can use CLEAN to strip non-printing characters and strip line breaks from text. For example, to clean text in cell A1:
The CLEAN function accepts just one argument, text, which can be a text string or number. CLEAN removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31), if any are found, and returns the result. Text without these characters is returned unchanged. Note that CLEAN will remove line breaks if found.
CLEAN will not remove extra space characters. To remove extra space, use the TRIM function. You can use CLEAN and TRIM together in one formula like this:
The CLEAN function removes the first 32 (non-printable) characters in the 7-bit ASCII code (values 0 through 31) from text. Unicode contains other non-printable characters that are not removed. To remove specific characters beyond the first 32 ASCII characters, you can use the SUBSTITUTE function with the CHAR function. For example to remove character 202:
First, you should know that Excel contains two functions, CLEAN and TRIM, that can automatically remove line breaks and extra spaces from text. For example to strip all line breaks from a cell, you could use:
The TRIM function is fully automatic. It removes removes both leading and trailing spaces from text, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a...