Excel String Tips – Part 3: TRIM and CLEAN functions

Excel String Tips – Part 3: TRIM and CLEAN functions

When using data from the internet, either copied from a website or contained within a downloaded file, string data can be “dirty”. This could be unprintable characters or white space at either the front or end of the string.

To remove excess space at the beginning and end of strings, you can use the TRIM function. This cuts off the white spaces that surround the text. For example, if you have the string ”     white space  ” in the cell A1, and you entered: =TRIM(A1), the resultant would be: “white space”. This is useful because when using left, right or mid functions, it will stop incorrect referencing.

The clean function is useful to remove unprintable characters from a string. If you try to print out the data, there is the possibility that it will not turn out the way you expect. Using the clean function removes the characters that may cause issues with printing. To use this function, simply use: =CLEAN(A1)

Hopefully you can use these functions to fix up text strings.

Come check out the directory for the rest of my excel tips!

Submeg

IT