Excel Formulas for Data Cleaning: Essential Functions Guide

Excel formulas are the foundation of data cleaning.
Knowing the right formulas can transform hours of manual work into minutes of automated cleaning.

This guide covers essential formulas every data cleaner needs to know.

🚨 Essential Cleaning Formulas

Text Cleaning

TRIM: Remove extra spaces

=TRIM(A2)  ' Removes leading, trailing, multiple spaces

CLEAN: Remove non-printable characters

=CLEAN(A2)  ' Removes hidden characters

SUBSTITUTE: Replace text

=SUBSTITUTE(A2, "old", "new")  ' Replace text
=SUBSTITUTE(A2, " ", "")  ' Remove all spaces

UPPER, LOWER, PROPER: Change case

=UPPER(A2)  ' Uppercase
=LOWER(A2)  ' Lowercase
=PROPER(A2)  ' Title case

Data Type Conversion

VALUE: Text to number

=VALUE(A2)  ' Convert text number to number

TEXT: Number to text

=TEXT(A2, "0.00")  ' Format as text

DATEVALUE: Text to date

=DATEVALUE(A2)  ' Convert text date to date

Data Validation

ISBLANK: Check if empty

=ISBLANK(A2)  ' Returns TRUE if blank

ISNUMBER: Check if number

=ISNUMBER(A2)  ' Returns TRUE if number

ISTEXT: Check if text

=ISTEXT(A2)  ' Returns TRUE if text

ISERROR: Check for errors

=ISERROR(A2)  ' Returns TRUE if error

Conditional Logic

IF: Conditional logic

=IF(A2>100, "High", "Low")  ' Simple condition
=IF(ISBLANK(A2), "Missing", A2)  ' Handle blanks

IFERROR: Handle errors

=IFERROR(A2/B2, "Error")  ' Return default if error

IFNA: Handle #N/A

=IFNA(VLOOKUP(A2, Table, 2, FALSE), "Not Found")

Text Extraction

LEFT, RIGHT, MID: Extract text

=LEFT(A2, 5)  ' First 5 characters
=RIGHT(A2, 5)  ' Last 5 characters
=MID(A2, 3, 5)  ' 5 characters starting at position 3

FIND, SEARCH: Find text

=FIND("@", A2)  ' Position of @ (case-sensitive)
=SEARCH("@", A2)  ' Position of @ (case-insensitive)

LEN: Text length

=LEN(A2)  ' Number of characters

Combining Formulas

Nested Formulas:

=TRIM(CLEAN(UPPER(A2)))  ' Clean, remove non-printable, uppercase
=IF(ISBLANK(A2), "", PROPER(A2))  ' Handle blank, then proper case
=VALUE(SUBSTITUTE(A2, "$", ""))  ' Remove $ and convert to number

✅ Best Practices

Formula Tips:

Combine functions for complex cleaning
Test on sample data first
Use absolute references ($) when copying
Document complex formulas

Performance:

Avoid volatile functions when possible
Use specific ranges, not entire columns
Consider Power Query for large datasets

🔗 Related Guides

Excel Data Cleaning Guide - Comprehensive guide
Excel Shortcuts - Speed up work
Excel for Beginners - Learn basics
Complete Excel Guide - Full guide

📌 Conclusion

Master these formulas to clean data efficiently. Start with TRIM, CLEAN, and SUBSTITUTE, then add more as needed.

✍️ Ready to automate cleaning?

👉 Try RowTidy for AI-powered data cleaning that's faster than formulas. Get started with a free trial.

This guide is part of our comprehensive series on Excel data management.