DEV Community

Cover image for 5 Excel & Google Sheets Formulas That Clean Messy Data 10x Faster
Fedora
Fedora

Posted on

5 Excel & Google Sheets Formulas That Clean Messy Data 10x Faster

#beginners #datascience #productivity #tutorial

Cleaning messy spreadsheets is one of the most time-consuming tasks for data analysts, marketers, and developers. Extra spaces, inconsistent formatting, mixed-up names, and unstandardized text often break database imports and analytics pipelines.

Here are 5 essential formulas to clean messy data in Microsoft Excel and Google Sheets 10x faster.

1. Remove Invisible & Non-Breaking Spaces: TRIM + CLEAN

When you copy-paste data from websites or PDFs, hidden ASCII characters and trailing spaces often cause lookup formulas (VLOOKUP or XLOOKUP) to return #N/A.

=TRIM(CLEAN(A2))
Enter fullscreen mode Exit fullscreen mode
  • CLEAN(A2): Strips non-printable ASCII characters (0 to 31).
  • TRIM(...): Removes leading, trailing, and excessive internal spaces.

2. Extract Text Before a Delimiter: TEXTBEFORE (Excel 365 / Sheets)

Need to extract the username from an email or the first word from a product code? Instead of nesting LEFT and FIND, use modern text splitting:

=TEXTBEFORE(A2, "@")
Enter fullscreen mode Exit fullscreen mode
  • For legacy Excel versions (2019 and older): 
=LEFT(A2, FIND("@", A2) - 1)
Enter fullscreen mode Exit fullscreen mode

💡 Need custom character extraction without memorizing syntax? You can generate exact formulas instantly with SheetMaster Formula Generator.

3. Dynamic Two-Way Lookups with XLOOKUP

Forget hardcoded column index numbers from VLOOKUP that break when you insert a new column. XLOOKUP looks in any direction (left or right) and handles missing values natively:

=XLOOKUP(D2, A2:A100, B2:B100, "Not Found", 0)
Enter fullscreen mode Exit fullscreen mode
  • D2: The lookup value.
  • A2:A100: The column to search within.
  • B2:B100: The return range.
  • "Not Found": Built-in default value if no match is found.

4. Standardize Case & Formatting: PROPER

Importing contact names in ALL CAPS or all lowercase looks unprofessional in email campaigns. Standardize full names into Title Case automatically:

=PROPER(TRIM(A2))
Enter fullscreen mode Exit fullscreen mode
  • Converts "JOHN DOE " → "John Doe".

5. Extract Domain Names from URL Strings

If you have a column of raw web URLs and need clean root domains for competitor analysis or backlink auditing:

=MID(A2, SEARCH("://", A2) + 3, SEARCH("/", A2, 9) - (SEARCH("://", A2) + 3))
Enter fullscreen mode Exit fullscreen mode

Summary & Free Productivity Tool

Cleaning data doesn't have to require manual formula debugging. If you want ready-to-use formula generators with real-time syntax checking, check out the free interactive tools at SheetMaster (100% free, zero signup required).

What are your favorite data cleaning tricks in spreadsheets? Let me know in the comments below! 🚀

Top comments (0)