Regex for Non-Programmers: 10 Patterns to Extract Anything from a Spreadsheet

In short: Regex is a text-matching language stable since the 1960s. Ten patterns handle most extraction and cleaning work in Google Sheets, Excel, and any modern editor. Learn them once, use them for the rest of your career. This guide gives you the ten worth memorizing plus the special characters that make them tick.

Key Takeaways

• Regex works in Google Sheets, Excel, Python, JavaScript, Notepad++, and every major programming language
• Google Sheets has three regex functions: REGEXMATCH, REGEXEXTRACT, REGEXREPLACE
• Ten patterns cover 90% of extraction and cleaning work in professional spreadsheets
• Marianne Gatlabayan uses regex to standardize thousands of email fields per month
• The cheat sheet at the end is worth bookmarking as a permanent reference

Why regex is the fastest skill you can add this week

Regex has been stable since the 1960s. It works in Google Sheets, Excel, Notepad++, VS Code, Python, JavaScript, and every major programming language. The patterns you learn once transfer to every tool you touch for the rest of your career. Very few professional skills have this kind of shelf life.

Learn ten patterns once, use them forever. Every hour spent learning regex compounds because the alternative (manual copy-paste, VLOOKUP acrobatics, hand-cleaning cells) never gets faster. Regex does the same job in a formula that runs in milliseconds.

Marketing operations, SEO, virtual assistant work, and any spreadsheet-heavy job get a permanent speed boost from regex. And 74% of Gen Z professionals use Google Sheets daily, which means the modern workforce sits on top of a spreadsheet layer where regex compounds in value with every year. Marianne Gatlabayan estimates regex saves her team dozens of hours per month across the link building and SEO workflows she leads.

Which three regex functions do you need in Google Sheets?

Google Sheets has three regex functions. Together they cover almost every extraction and cleaning job. Master these three and you have the full toolkit.

Each function takes at least two arguments: the cell to check and the regex pattern in quotes. REGEXREPLACE takes a third argument, which is the replacement text.

In Excel, similar functions arrived in Microsoft 365 in 2024. Same syntax, same behavior. Older Excel versions require Power Query or VBA macros to do the same work, which is why regex adoption in Excel-first organizations lags behind Google Sheets teams by several years.

Pattern 1: How do you extract any email address from a cell?

Emails come mixed into notes fields, contact form submissions, and free-text comments. Pulling them into their own column makes contact management possible.

The pattern:

[\w.+-]+@[\w-]+\.[\w.-]+

The formula:

=REGEXEXTRACT(A2, "[\w.+-]+@[\w-]+\.[\w.-]+")

Point it at a cell that mixes text and an email. It returns just the email. The pattern reads as: one or more letters, digits, dots, plus signs, or hyphens, then an @, then one or more letters, digits, or hyphens, then a dot, then one or more letters, digits, dots, or hyphens.

Combine with QUERY to build an email extractor across a whole column of messy notes. Wrap the REGEXEXTRACT in ARRAYFORMULA to apply it down an entire column without dragging: =ARRAYFORMULA(IF(A2:A="","",REGEXEXTRACT(A2:A, "[\w.+-]+@[\w-]+\.[\w.-]+"))).

Pattern 2: How do you extract a phone number in any format?

Phone numbers appear in 10 formats across a typical dataset. This pattern catches most of them at once.

\+?\d{1,3}?[-.\s]?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}

That handles U.S. and international formats. The pattern reads as: an optional plus sign, 1 to 3 optional country code digits, an optional separator (dash, dot, or space), then the classic 3-3-4 US format with optional parentheses around the area code and any of the same separators between groups.

In Sheets:

=REGEXEXTRACT(A2, "\+?\d{1,3}?[-.\s]?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}")

To strip all formatting and keep just digits:

=REGEXREPLACE(A2, "\D", "")

The \D matches anything that is not a digit. Replacing with an empty string leaves only the digits. Now (555) 123-4567 becomes 5551234567, which imports cleanly into any CRM that stores phones as digit-only strings.

Pattern 3: How do you extract a URL from mixed text?

URLs get pasted into notes, comments, and social media exports. Pulling them into their own column enables link tracking and outreach automation.

https?:\/\/[^\s]+

Formula:

=REGEXEXTRACT(A2, "https?:\/\/[^\s]+")

The ? after https makes the s optional, so it catches both http and https. [^\s]+ matches one or more characters that are not whitespace, which grabs everything up to the first space. This works for almost all URLs because URLs cannot contain spaces.

To extract only the domain, add a capture group:

=REGEXEXTRACT(A2, "https?:\/\/(?:www\.)?([^\/\s]+)")

The parentheses around [^\/\s]+ create a capture group. REGEXEXTRACT returns the capture group instead of the full match when parentheses are present. The (?:www\.)? is a non-capturing group that optionally matches "www." at the start. Result: example.com from any URL, regardless of protocol or www prefix.

Pattern 4: How do you extract dates in common formats?

Date extraction from text notes is common in customer service logs, meeting minutes, and CRM comments. The pattern below matches the two most common date formats.

For dates like 2026-09-24 or 09/24/2026:

\d{4}[-/]\d{2}[-/]\d{2}|\d{2}[-/]\d{2}[-/]\d{4}

Formula:

=REGEXEXTRACT(A2, "\d{4}[-/]\d{2}[-/]\d{2}|\d{2}[-/]\d{2}[-/]\d{4}")

The | means OR. The pattern matches either format and returns whichever appears first in the cell. The \d{4} requires exactly four digits, \d{2} requires exactly two. The character class [-/] matches either a hyphen or a forward slash, which covers ISO dates and US-style dates in one pattern.

For extracting textual dates like "September 24, 2026" or "24 Sep 2026", the regex gets much more complex. Better to use a dedicated date parser library in Python or Apps Script for those cases. Regex handles the numeric formats well and struggles with natural language.

Pattern 5: How do you clean weird whitespace from cells?

Data pasted from PDFs, web pages, or emails often carries invisible garbage: non-breaking spaces, tab characters, multiple spaces, or line breaks in the middle of a value. These break VLOOKUP and QUERY matches because "New York" and "New York" look identical but are different strings.

=REGEXREPLACE(A2, "\s+", " ")

That collapses any run of whitespace into a single space. \s matches any whitespace character including tabs, newlines, and non-breaking spaces. The + matches one or more. Trailing and leading spaces get flattened to single spaces.

Follow with TRIM() for the final polish:

=TRIM(REGEXREPLACE(A2, "\s+", " "))

TRIM removes the remaining leading and trailing single spaces. This is the single most useful cleanup formula in a spreadsheet. Marianne Gatlabayan wraps every text field imported from a PDF or web scrape in this combination before doing any further work.

Patterns 6 to 8: The SEO and marketing extractors

Three patterns that show up constantly in marketing operations work.

6. Extract hashtags from a social post:

=REGEXEXTRACT(A2, "#\w+")

Returns the first hashtag in the cell. Use REGEXEXTRACT with a global flag in Apps Script to grab all hashtags at once. In Sheets, this pattern returns only the first match per cell.

7. Extract UTM source from a URL:

=REGEXEXTRACT(A2, "utm_source=([^&]+)")

The capture group ([^&]+) matches everything after utm_source= up to the next & character. Perfect for auditing where traffic actually came from when your CRM records the landing page URL.

8. Extract file extension from a URL or filename:

=REGEXEXTRACT(A2, "\.([a-zA-Z0-9]+)$")

The $ anchors the match to the end of the string. The capture group grabs everything after the final dot. Returns "pdf" from "report.pdf" or "png" from "image.png".

Each of these is one formula that replaces a manual copy-paste task. Chain them together in helper columns to progressively enrich a raw text column into structured data.

Pattern 9: How do you validate that a cell contains a valid email?

Instead of extracting, sometimes you just want a yes or no answer. This is the pattern for form validation, data quality checks, and CRM cleanup flags.

=REGEXMATCH(A2, "^[\w.+-]+@[\w-]+\.[\w.-]+$")

The ^ and $ anchor the pattern to the start and end of the string. This checks whether the ENTIRE cell is a valid email address, not just whether it contains one. Cells with "email: john@example.com" fail because they contain extra text.

Returns TRUE or FALSE. Wrap in IF for custom messages:

=IF(REGEXMATCH(A2, "^[\w.+-]+@[\w-]+\.[\w.-]+$"), "Valid", "Fix this")

Now the column shows "Valid" or "Fix this" instead of TRUE or FALSE, which reads better in a status column for a CRM cleanup project. Combine with conditional formatting to color the invalid cells red for quick visual scanning.

Pattern 10: How do you split one messy cell into three clean columns?

Data arrives as a single free-text field more often than anyone plans for. Contact forms, CSV exports from legacy tools, and Slack pastes all produce cells that need parsing into structured columns.

Pull several fields from one messy cell using capture groups.

Data cell: John Smith, john@example.com, +1-555-123-4567

Name:  =REGEXEXTRACT(A2, "^([^,]+)")
Email: =REGEXEXTRACT(A2, "([\w.+-]+@[\w-]+\.[\w.-]+)")
Phone: =REGEXEXTRACT(A2, "(\+?\d[-\d]{9,})")

Three formulas parse one column into three clean columns. This is the fastest way to structure data that arrived as freeform text.

The ^([^,]+) pattern captures everything from the start of the string up to the first comma. The email pattern reuses the standard email regex. The phone pattern uses a simplified version that catches most formats with 10 or more digits.

For truly complex parsing, combine regex with SPLIT() and array operations. But for the common case of "three fields separated by commas or semicolons in one cell", three REGEXEXTRACT formulas do the whole job.

Which regex special characters do you actually need to know?

Memorize this table. It unlocks every pattern in this post and most patterns you will ever need.

Ten special characters cover 90% of practical regex use. The remaining regex features (lookaheads, back-references, atomic groups, possessive quantifiers) exist for edge cases you probably will never hit in spreadsheet work.

Two useful additions to know exist but rarely need memorizing. \D is the inverse of \d, meaning any non-digit. \W is the inverse of \w. \S is the inverse of \s. Uppercase versions negate the lowercase versions. Save this for when you need to strip all letters from a phone field or all numbers from a text field.

When does regex break down?

Regex fails on nested structures. Do not use it to parse HTML, XML, or JSON. Those have opening and closing tags that can nest arbitrarily deep, which regex cannot track. The famous Stack Overflow answer that "you cannot parse HTML with regex" is technically true, though for very simple cases regex works well enough as a hack.

Regex also struggles with international formats. A phone number regex tuned for U.S. numbers will miss half of what a UK or Philippine dataset throws at it. When you need global coverage, use a dedicated library like phonenumbers in Python. The library maintains lookup tables for every country's phone format and handles the edge cases regex cannot.

For everything else, regex is fine. Text extraction, format validation, cleanup, and parsing of simple structured data all fall within regex's sweet spot. The failure modes are well understood, so you can pick the right tool by knowing the boundaries.

Frequently Asked Questions

Does regex work in Google Sheets on mobile?

Yes. REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE all work in the Google Sheets mobile app. The syntax is identical to desktop. Complex formulas are hard to type on a phone but existing formulas work fine.

Can I use these patterns in Excel?

Microsoft 365 added REGEX functions in 2024. The syntax is nearly identical. Older Excel versions require a VBA macro or the Power Query editor. If you are on the current Microsoft 365, everything in this guide works with minor naming changes.

How do I test a regex before using it in a formula?

Regex101.com is the standard free tester. Paste your pattern and sample text. It highlights matches and explains each part of the pattern in a side panel. This is how Marianne Gatlabayan debugs every new pattern before dropping it into a Sheet.

Is regex case-sensitive?

Yes by default. Add (?i) at the start of the pattern to make it case-insensitive: (?i)hello matches Hello, HELLO, and hello. Not all regex flavors support this inline flag. Google Sheets does.

Are these patterns safe to use in production spreadsheets?

Yes for text extraction and validation. Do not rely on regex for legal or compliance-critical validation like tax IDs, which have checksum rules regex cannot enforce. For those cases, use a proper validation library or a dedicated service.

What to read next

• The Google Sheets QUERY Function Explained for filtering data after regex cleans it
• Clean CRM Data with Python Pandas for regex at scale
• The Beginner's Guide to Automating Recurring Reports with Google Apps Script for scheduling regex-based cleaning

About the Author: Marianne Gatlabayan is a Systems Strategist and Industrial Engineer focused on optimizing operations, automating workflows, and driving business growth. With over a decade of experience in analytics, reporting, and process improvement, she helps organizations build scalable systems that turn complexity into measurable results. In her free time, Marianne enjoys discovering and sipping her favorite coffee bean of the week. Connect on LinkedIn.

Sources

• Google Sheets Regex Documentation
• Google Sheets Usage Statistics
• Regular Expressions Reference (MDN)