Here, we will learn how to use the Excel text functions: CLEAN and TRIM. Later, we will also learn how to clean data by combining TRIM and CLEAN in one formula.
Excel CLEAN function
The Excel CLEAN function removes line breaks and non-printable characters from the given text string. For example, to clean text in A1, use CLEAN like this: =CLEAN(A1). The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with CLEAN if you are working with numeric values. The formula in B4, copied down, is:
=CLEAN(A4)
Excel TRIM function
The Excel TRIM function removes line breaks, extra spaces between words and leading and trailing spaces from the given text string. For example, the formula =TRIM(" Learn Excel ") returns "Learn Excel". The function always returns the result in the text format even if the return value is a numeric value. Use VALUE with TRIM if you are working with numeric values. The formula in E4, copied down, is:
=TRIM(D4)
Cleaning data by combining TRIM and CLEAN
To remove line breaks, non-printable characters and unnecessary spaces from a text string, you can use a formula based on the CLEAN and TRIM functions.
In A12, we have a text string that contains unnecessary spaces, non-printable characters and a line break. The goal is to remove all these unnecessary things so that we can have a clean text string. The formula in B12, copied down, is:
=TRIM(CLEAN(A12))
You have successfully learnt how to use the Excel text functions: CLEAN and TRIM. You have also learnt how to clean data by combining TRIM and CLEAN in one formula! I hope this post helped you.
