Data cleansing functions

When you import data from an external source, you will often find it isn’t in a desirable format for you to analyse. You will need to clean your data up first and thankfully there are lots of in-built Excel functions which can help.




CLEAN can be used to remove non-printable characters you’ve imported. Or in this example to remove a line break from within a cell. The formula used was:



TRIM will remove any leading or trailing spaces. We have used the formula:



SUBSTITUTE can be used to replace one string of characters with another. In this example we’ve imported an HTML break tag and want to replace it with a comma and a space:

=SUBSTITUTE(C5,”<BR>”,”, “)


PROPER converts your text into ‘proper’ case with the beginning of each word capitalised. You can also use UPPER or LOWER if you want to convert all your text into one or the other:


Leave a Reply

Your email address will not be published. Required fields are marked *