How to Use the CLEAN, CONCATENATE, and EXACT Text Functions to Clean Data in Excel

By Stephen L. Nelson, E. C. Nelson

One problem you may find with importing data into Excel is that your text labels may not look quite right. You can keep your data clean with these text functions.

The CLEAN function

Using the CLEAN function removes nonprintable characters text. For example, if the text labels shown in a column are using crazy nonprintable characters that end up showing as solid blocks or goofy symbols, you can use the CLEAN function to clean up this text. The cleaned-up text can be stored in another column. You can then work with the cleaned text column.

The CLEAN function uses the following syntax:

CLEAN(text)

The text argument is the text string or a reference to the cell holding the text string that you want to clean. For example, to clean the text stored in Cell A1, use the following syntax:

CLEAN(A1)

The CONCATENATE function

The CONCATENATE function combines, or joins chunks of text into a single text string. The CONCATENATE function uses the following syntax:

CONCATENATE(text1,text2,text3,...)

The text1, text2, text3, and so on arguments are the chunks of text that you want to combine into a single string. For example, if the city, state, and ZIP code were stored in fields named city, state and zip, you could create a single text string that stores this information by using the following syntax:

CONCATENATE(city,state,zip)

If city were Redmond, state were WA, and zip were 98052, this function returns this text string:

RedmondWA98052

The smashed-together nature of the concatenated city, state, and ZIP code information isn’t a typographical mistake, by the way. To concatenate this information but include spaces, you need to include spaces as function arguments. For example, the following syntax:

CONCATENATE("Redmond", " ","WA", " ","98052")

returns the text string

Redmond WA 98052

The EXACT function

The EXACT function compares two text strings. If the two text strings are exactly the same, the EXACT function returns the logical value for true, which is 1. If the two text strings differ in any way, the EXACT function returns the logical value for false, which is 0. The EXACT function is case-sensitive. For example, Redmond spelled with a capital R differs from redmond spelled with a lowercase r.

The EXACT function uses the following syntax:

EXACT(text1,text2)

The text1 and text2 arguments are the text strings that you want to compare. For example, to check whether the two strings “Redmond” and “redmond” are the same, use the following formula:

EXACT("Redmond","redmond")

This function returns the logical value for false, , because these two text strings don’t match exactly. One begins with an uppercase R and the other begins with a lowercase r.