Excel Data Cleaning: Top Formulas and Functions

June 13, 2024

When it comes to data cleaning in Excel, several formulas are commonly used to manipulate, format, and clean data efficiently. Here are some of the most frequently used formulas for data cleaning:

1. TRIM

The TRIM function removes extra spaces from text, leaving only single spaces between words.

Formula:

=TRIM(text)

Example:
If cell A1 contains ” Hello World “, =TRIM(A1) will return “Hello World”.

2. CLEAN

The CLEAN function removes all non-printable characters from text.

Formula:

=CLEAN(text)

Example:
If cell A1 contains “Hello\x0AWorld”, =CLEAN(A1) will return “HelloWorld”.

3. LEFT, MID, RIGHT

These functions extract parts of a text string based on position.

Formulas:

=LEFT(text, num_chars)
=MID(text, start_num, num_chars)
=RIGHT(text, num_chars)

Example:
If cell A1 contains “HelloWorld”:

=LEFT(A1, 5) returns “Hello”.
=MID(A1, 6, 5) returns “World”.
=RIGHT(A1, 5) returns “World”.

4. SUBSTITUTE

The SUBSTITUTE function replaces occurrences of a specified text string with another text string.

Formula:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Example:
If cell A1 contains “Good Morning”, =SUBSTITUTE(A1, "Morning", "Evening") returns “Good Evening”.

5. TEXTJOIN

The TEXTJOIN function concatenates a range or list of text strings using a delimiter.

Formula:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Example:
If cells A1 to A3 contain “Apple”, “Banana”, “Cherry”, =TEXTJOIN(", ", TRUE, A1:A3) returns “Apple, Banana, Cherry”.

6. FIND and SEARCH

These functions locate the position of a substring within a text string.

Formulas:

=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])

Example:
If cell A1 contains “HelloWorld”:

=FIND("World", A1) returns 6.
=SEARCH("world", A1) returns 6 (case-insensitive).

7. IFERROR

The IFERROR function returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

Formula:

=IFERROR(value, value_if_error)

Example:
=IFERROR(VLOOKUP("xyz", A1:B10, 2, FALSE), "Not Found") returns “Not Found” if the VLOOKUP results in an error.

8. TEXT to Columns

While not a formula, the Text to Columns feature is crucial for data cleaning, splitting a single column of data into multiple columns based on delimiters like commas, spaces, or tabs.

How to Use:

Select the column to split.
Go to the Data tab and click Text to Columns.
Choose the delimiter and follow the prompts.

9. VALUE

The VALUE function converts text that appears in a recognized format (a number, date, or time) into a number.

Formula:

=VALUE(text)

Example:
If cell A1 contains “123”, =VALUE(A1) returns 123 as a numeric value.

10. PROPER, UPPER, LOWER

These functions change the case of text.

Formulas:

=PROPER(text)  // Capitalizes the first letter of each word
=UPPER(text)   // Converts all text to uppercase
=LOWER(text)   // Converts all text to lowercase

Example:
If cell A1 contains “hello world”:

=PROPER(A1) returns “Hello World”.
=UPPER(A1) returns “HELLO WORLD”.
=LOWER(A1) returns “hello world”.

These formulas and tools are essential for effective data cleaning and preparation in Excel, helping you ensure your data is accurate and properly formatted.

EMZ: Data visualization, Data Artis