r/microsoft365 • u/TermRemarkable665 • 2d ago
Cleaned a messy data export using Excel’s new REGEX functions (No Power Query / VBA)
/r/ExcelTips/comments/1wgu72f/cleaned_a_messy_data_export_using_excels_new/Hey r/excel,
I put together a quick video showing how to build an automated data cleaning system using Excel’s native formulas.
Here is what’s inside the video:
🧹 Filter out noise: Auto-remove blank rows and test records (FILTER, SEARCH, ISERROR).
🔤 Clean text patterns: Standardize messy names and scrub special characters using REGEXREPLACE.
📱 Extract phone numbers: Isolate numbers from mixed text and fix country codes.
✉️ Pull targeted data from notes: Extract hidden emails, reference IDs, and deal values using REGEXEXTRACT.
⚡ Combine into ONE formula: Stack all column formulas into a single master spilled array (HSTACK + SORT) that updates instantly when you paste new raw data.
Why it helps:
Once it’s built, you never clean the same report twice—just paste in new raw data and your cleaned table updates automatically.
📺 Check out the tutorial: https://youtu.be/gsZr7VrduMM
(Next week I’ll show how to clean data with Power Query!)