Regex Recipes for CRM and CSV Data Cleanup

Regex Recipes for CRM and CSV Data Cleanup

Dirty CRM data costs real money: bounced emails, duplicate contacts, botched mail merges. Here are 5 battle-tested regex patterns that clean the most common issues. Test each one in the Regex Tester before running on production data.

1. Phone Number Normalization

Strip everything except digits and leading +:

Pattern: [^\d+]
Replace: (empty)
Flags:   g

Input: (555) 123-4567 → Output: 5551234567

Input: +1-800-555-0199 → Output: +18005550199

Then format programmatically:

const digits = phone.replace(/[^\d+]/g, '');
// Format US: +1 (XXX) XXX-XXXX

2. Email Validation

Catch obviously invalid emails before they enter your CRM:

Pattern: ^[^\s@]+@[^\s@]+\.[^\s@]+$

Matches: [email protected], [email protected]

Rejects: user @example, @missing.com, no-at-sign.com

This is a practical filter, not RFC 5322 compliant. For production, pair with MX record lookup.

3. Remove Duplicate Spaces

CSV exports and copy-paste often introduce extra whitespace:

Pattern: \s{2,}
Replace: (single space)
Flags:   g

Input: John Smith Jr → Output: John Smith Jr

Also trim leading/trailing whitespace with ^\s+|\s+$ (flags: gm for multiline).

4. Extract Domain from URL

Pull company domains from a list of website URLs:

Pattern: https?:\/\/([^\/]+)
Capture group 1 = domain

Input: https://www.acme-corp.com/about → Group 1: www.acme-corp.com

To strip www.:

Pattern: https?:\/\/(?:www\.)?([^\/]+)

5. Clean CSV Quote Artifacts

CSV parsers sometimes leave stray quotes and spaces:

Pattern: ^[\s"]+|[\s"]+$
Replace: (empty)
Flags:   g (apply per field)

Input: " Acme Corp " → Output: Acme Corp

For escaped double-quotes inside fields:

Pattern: ""
Replace: "

Putting It All Together

import re

def clean_crm_row(row: dict) -> dict:
    if 'phone' in row:
        row['phone'] = re.sub(r'[^\d+]', '', row['phone'])
    if 'email' in row:
        row['email'] = row['email'].strip().lower()
    if 'company' in row:
        row['company'] = re.sub(r'\s{2,}', ' ', row['company']).strip()
    return row

Paste each pattern into the Regex Tester with sample data to verify matches and captures before applying at scale.

Related