Text functions in Excel for data cleansing

 

If you are a regular user of Excel and deal with lot of text data, then mastering text functions in Excel is crucial. Excel has in-built text functions to help cleansing data for you. Below are some the text functions in excel helpful for quick data cleansing

 

1.CLEAN: 

CLEAN function clears any non-printable characters from the text. Non-printable characters are first 32 to characters of ASCII table. For example in cell A1, enter the formula “=CHAR(27)” this displays a character “” as shown below, which represents escape character and it is a non-printable character.

Now, Add a text to this cell using formula below so that the cell has combination of printable and non-printable characters.

    “=CHAR(27)&” Hello”

To clean cell A1, enter the formula “=CLEAN(A1)” in cell A2.

 

2. TRIM

Trim is useful function in removing leading and trailing spaces from a text in the cell. But it doesn’t remove spaces between the words.

For example, A1 = TRIM(“     Hello”) prints Hello in cell without spaces

 

3. REPLACE/SUBSTITUTE

Both REPLACE and SUBSTITUTE functions are used to replace a specific segment of a text but has slightly different syntax. Enter a text “Hi There” in cell E4 and E5 and below are the examples of replacing the word “Hi” with “Hello” using REPLACE and SUBSTITUTE

REPLACE syntax “REPLACE(E3,1,2,"Hello")

SUBSTITUTE syntax SUBSTITUTE(E4,"Hi","Hello",1)

REPLACE uses start number, number of characters and new word to be replaced in a text as an argument, whereas SUBSTITUTE uses text to be replaces and new text and instance as arguments.