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

Posted by:submeg

Musician, Writer and Inspirer. I discuss the projects I am working on, the services I provide and my general thoughts.

One thought on “Excel String Tips – Part 3: TRIM and CLEAN functions

Tell me what you think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s