Regex Cleans the Strings Excel Can't
Excel is great at math and filters, weak at 'find the pattern inside this text.' That's where regular expressions shine. A zip code hides inside '123 Main St, Springfield 62704'; a phone shows as '(217) 555-0192'. Regex pulls the useful part out and throws the rest away, in one expression over every row.
In pandas you rarely call re directly on each cell. You use the .str accessor, which applies a regex to a whole column at once. That turns a 12,000-row cleanup into a single line instead of 12,000 manual edits.
Step 1: Set Up re and a Column
Run 'import re' and 'import pandas as pd'. re is built into Python, so no install is needed; pandas you already have from setup.
Run 'df = pd.read_csv("customers.csv")' with columns Name, Address, Phone, and Email. Print 'df.head()' to see the inconsistent formatting you're about to fix.
Run 'df['Phone'].info()' is wrong; use 'df['Phone'].dtype'. It should be 'object' (text). Regex only works on strings, so a numeric column must be cast with 'astype(str)' first.
Step 2: Extract a Zip Code
Run 'df['Zip'] = df['Address'].str.extract(r'(\d{5})')'. The '\d{5}' matches exactly five digits, and the parentheses capture just that group into the new Zip column.
If some rows show '62704-1234', use r'(\d{5})(?:-\d{4})?' to grab the five-digit base and ignore the extra four. Without the optional group the trailing '-1234' can break a later join on Zip.
Run 'df['Zip'].notna().mean()'. If it's 0.96 you extracted 96 percent of rows; the other 4 percent likely have a non-standard address worth a manual look.
I recommend checking the match rate with notna().mean() after every extract. A pattern that catches only 60 percent of rows is quietly dropping 40 percent of your data, and you won't notice until a region vanishes from a report.
Step 3: Strip Phone Formatting
Run 'df['Phone'] = df['Phone'].astype(str).str.replace(r'\D', '', regex=True)'. The '\D' matches anything that isn't a digit, so '(217) 555-0192' becomes '2175550192'.
Run 'df = df[df['Phone'].str.len() == 10]'. This drops rows with 7 or 11 digits that signal a typo or a fake entry. On a 12,000-row file this may remove about 340 bad phones, roughly 2.8 percent.
Run 'df['Phone'] = df['Phone'].str.replace(r'(\d{3})(\d{3})(\d{4})', r'(\1) \2-\3', regex=True)'. This puts the cleaned digits back as '(217) 555-0192' for a reader-friendly export.
Step 4: Validate and Fix Emails
Run 'bad = df[~df['Email'].str.contains(r'^[^@]+@[^@]+\.[^@]+$', regex=True)]'. This pattern requires one @, a dot after it, and text on both sides. Rows failing it have a typo like 'jane[at]co'.
Run 'df['Email'] = df['Email'].str.lower()'. Email domains are case-insensitive, but mixed case 'John@Co.com' breaks a later dedupe that treats it as a different person. Lowercasing fixes that silently.
Run 'df['Email'] = df['Email'].str.strip()'. A leading space from a pasted export turns ' a@co.com' into a second, broken record. Strip before you dedupe or you'll keep both.
A common mistake is writing a giant email regex that rejects valid addresses like plus tags 'name+team@co.com'. Keep the pattern simple and permissive; catch the obvious typos, don't try to enforce a perfect spec that real inboxes break.
Step 5: Parse Prices and Dates
Run 'df['Price'] = df['Price'].str.replace(r'[$,]', '', regex=True).astype(float)'. This turns '$1,200.50' into 1200.5 so you can sum it. A comma inside the number is the usual reason a sum returns an error.
Run 'df['Date'] = pd.to_datetime(df['Date'], errors="coerce")'. The errors='coerce' turns unparseable values into NaT instead of crashing, so one bad '2024-13-40' doesn't kill the whole column.
Run 'df['Date_bad'] = df['Date'].isna()'. This tags rows where the date failed, so you can report '42 rows need a manual date fix' rather than guessing why a total looks low.
Worked Example: 3,400 Bad Name Rows
A 12,000-row customer file had names like 'Smith, John,' with a trailing comma and 'Jane,, Doe' with a double comma. The pattern r',{2,}' caught 3,400 rows with repeated commas, about 28.3 percent of the file. Fixing them with 'df['Name'] = df['Name'].str.replace(r',{2,}', ',', regex=True).str.strip(r',')' cleaned the field in one pass. Before regex I had hand-edited maybe 200 a day; the script did the rest before lunch.
Run 'print((df['Name'].str.contains(r',{2,}')).sum())'. Seeing 3400 tells you the scale of the problem and proves the fix mattered, not just 'some rows looked off'.
After the replace, rerun the count; it should read 0. A non-zero remainder means your pattern missed a variant and you check the leftover rows directly.
Step 6: Test a Pattern Before You Trust It
Run 're.search(r'\d{5}', '62704')' in isolation before applying to 12,000 rows. If it returns None on a value you expected to match, fix the pattern on the small case first.
Paste the pattern into a site like regex101.com with three sample rows: a good one, a bad one, and an edge case. Confirming all three behave right takes a minute and saves a corrupted column.
Run 'df[df['Zip'].isna()].head(10)' to read the rows your pattern missed. Those ten examples tell you whether to tighten or loosen the regex far better than guessing.
Python Regex vs Excel Text Tools
Excel's TEXT and FIND functions handle simple splits, but they can't express 'five digits optionally followed by a dash and four more.' For that you need regex, and Excel added it late via the REGEX functions. Our regex functions in Excel guide shows the in-sheet version when your data stays in a workbook.
Step 7: Pull Numbers Out of Mixed Text
Run 'df["Qty"] = df["Note"].str.extract(r"(\d+)")'. This pulls the first run of digits from a free-text Note column into its own Qty field, so 'Order 12 boxes' yields 12 you can sum.
Run 'df["Price"] = df["Text"].str.extract(r"\$(\d+\.?\d*)")'. The pattern catches a dollar amount like 1200.50 and drops the symbol, giving a clean number ready for math.
Use extract, not replace, when you want the value in a new column rather than the old text cleaned. A common mistake is replacing the surrounding words and keeping a half-address string; extract gives you just the number and leaves the source untouched for audit. I keep the original column next to the extracted one so anyone can check the parse was right.
Build a Cleaning Checklist
Treat cleanup as a checklist, not a one-off. For every export, confirm the zip extracted, the phone stripped to ten digits, the email valid, and the price parsed to a number, then log how many rows each step fixed. A printed line like '3,400 names cleaned' is your evidence the step ran, and a drop in that number later tells you the source got cleaner or your pattern broke without you noticing.
Keep the original column next to the cleaned one. When you extract a zip into a new 'Zip' field, leave 'Address' in place so anyone can check the parse was right by reading both side by side. A cleanup that overwrites the source makes the result impossible to audit, and audits are exactly when you need to prove the number is real rather than guessed by a pattern.
Don't trust a pattern that matches too much. A loose regex like '.*' catches everything and tells you nothing; a tight one like '\d{5}' catches exactly five digits and nothing else. When a pattern's match rate jumps from 96 to 100 percent after a small change, check the rejects, because you may have widened it enough to swallow bad data you wanted to flag as a problem.
Save the cleaned file with a clear name and date. Write 'customers_2024_clean.csv' next to the raw 'customers.csv', so the next person grabs the right one without asking. A folder of 'final.csv', 'final_v2.csv', and 'really_final.csv' is how a good dataset turns into a guessing game, and regex can't save you from a bad filename that hides which version is current.
Next Steps
Regex is one tool in cleanup. Pair it with deduplication and type fixes so the column you extract is not just formatted but correct, then the analysis on top of it means something.
Wrap your zip, phone, and email fixes in one 'clean_customer(df)' function. Reuse it on every new export so cleaning is one call, not a copied block of lines each time.
After cleaning, print the count of rows fixed per field and save it with the output. A line like '3,400 names cleaned' is your evidence the step ran and your manager can trust the file.
