REGEXEXTRACT vs LEFT/MID/RIGHT in Google Sheets
Both extract substrings. Both are foundational for data cleanup. But LEFT/MID/RIGHT work on positions — "give me the first N characters, or characters starting at position K" — while REGEXEXTRACT works on patterns — "give me whatever matches this shape." One is elementary; the other is powerful. Knowing which to reach for saves hours on messy data.
⚡ Quick Answer
Use LEFT/MID/RIGHT when the text has a fixed structure — same length, same position, predictable delimiters. Use REGEXEXTRACT when the text varies but has a consistent pattern — emails, phone numbers, URLs, IDs embedded in messy strings. Position functions are simpler; regex is more flexible. Reach for regex when position stops being reliable.
📋 What's in this comparison
Position-based extraction: LEFT/MID/RIGHT
The classic trio. Every spreadsheet user learns these first because they map directly to how spreadsheets think — cells contain strings, strings have positions, extract by position.
=LEFT(text, num_chars) // first N characters
=RIGHT(text, num_chars) // last N characters
=MID(text, start, num_chars) // N characters from position
Combine with FIND to make them dynamic:
=LEFT(A2, FIND(" ", A2) - 1) // everything before the first space
=MID(A2, FIND("@", A2) + 1, LEN(A2)) // everything after @ (safe upper bound)
Position-based strengths
- Simple mental model — count characters
- Fast on large datasets
- Works in every spreadsheet app ever made
- Easy to explain to non-technical users
- Reliable when the input format is truly consistent
Position-based weaknesses
- Fragile when input varies — one row with an extra space breaks the extraction
- Verbose for complex patterns — nested LEFT + FIND + LEN gets ugly fast
- Can't handle multi-part matching (e.g., "the number that appears after 'Order:'")
- No pattern flexibility — can't say "match 3 digits, then a dash, then 4 digits"
Pattern-based extraction: REGEXEXTRACT
=REGEXEXTRACT(text, regular_expression)
REGEXEXTRACT returns the first substring that matches your regex pattern. Regex (regular expressions) is a mini-language for describing text patterns — "one or more digits," "any letter followed by a period," "a valid email shape."
=REGEXEXTRACT(A2, "\d+") // first sequence of digits
=REGEXEXTRACT(A2, "[a-zA-Z]+") // first sequence of letters
=REGEXEXTRACT(A2, "\d{3}-\d{4}") // matches "123-4567" shape
Pattern-based strengths
- Handles varying input formats
- Extracts based on shape, not position — "the phone number, wherever it is"
- One formula replaces nested LEFT/MID/RIGHT/FIND stacks
- Reusable patterns (email, URL, phone) transfer across projects
- Can capture specific parts with parentheses (groups)
Pattern-based weaknesses
- Steeper learning curve — regex has its own syntax
- Slightly slower on massive datasets
- Cryptic patterns can be hard to read months later
- Non-programmers on your team may struggle to modify
Side-by-side comparison
| Aspect | LEFT/MID/RIGHT | REGEXEXTRACT |
|---|---|---|
| Extraction method | By character position | By pattern match |
| Learning curve | Trivial | Steeper (regex syntax) |
| Handles varying input | Poorly (rigid positions) | Excellent (flexible patterns) |
| Complex patterns | Nested formulas needed | One formula |
| Speed (large datasets) | Faster | Slightly slower |
| Readability | Clear for simple cases | Requires regex fluency |
| Excel compatibility | Universal | Excel 365 (2024+) only |
| Common use | Fixed-format IDs, dates, prefixes | Emails, URLs, phone numbers, IDs from mixed text |
| Not-found handling | Empty/incorrect result | Returns #N/A |
| Case sensitivity | Case-sensitive by default | Case-sensitive by default |
Same problem, both approaches
Extract year from an ID like "USA-2026-00417"
LEFT/MID — clear winner
=MID(A2, 5, 4)
Fixed positions. MID at 5, take 4. Done.
REGEXEXTRACT — overkill
=REGEXEXTRACT(A2, "\d{4}")
Works but adds complexity for no gain.
Winner: LEFT/MID. Fixed structure = position wins.
Extract email from a messy string
LEFT/MID/FIND — fragile
=MID(A2, FIND("@", A2) - X,
FIND(" ", A2, FIND("@", A2)) - 1)
-- X depends on where the email starts.
-- If format varies, this breaks.
You'd need helper columns to find the start of the email string.
REGEXEXTRACT — clean
=REGEXEXTRACT(A2,
"[\w.]+@[\w.]+\.\w+")
Matches email shape anywhere in the text.
Winner: REGEXEXTRACT. Variable structure = pattern wins.
Extract phone number from mixed content
LEFT/MID/FIND
-- Nearly impossible without knowing
-- exactly where phone starts.
-- Would need helper columns and
-- complex FIND chains.
REGEXEXTRACT — trivial
=REGEXEXTRACT(A2,
"\d{3}-\d{3}-\d{4}")
Matches "555-123-4567" shape.
Winner: REGEXEXTRACT. This is what regex was invented for.
Extract first name from "Firstname Lastname"
LEFT + FIND — familiar
=LEFT(A2, FIND(" ", A2) - 1)
REGEXEXTRACT — also fine
=REGEXEXTRACT(A2, "^\S+")
"^\S+" = first sequence of non-space characters.
Winner: Toss-up. Both work; use whichever your team reads faster.
A tour of essential regex patterns
Even a basic regex vocabulary unlocks 80% of extraction needs. Here are the patterns worth memorizing:
| Pattern | Matches | Example use |
|---|---|---|
\d | Any digit | \d+ = one or more digits |
\w | Letter, digit, or underscore | \w+ = an identifier |
\s | Any whitespace | \s+ = a run of spaces |
. | Any character | Catches everything except newline |
+ | One or more | \d+ = 1+ digits |
* | Zero or more | \w* = 0+ word chars |
? | Optional (0 or 1) | colou?r = "color" or "colour" |
{n} | Exactly n times | \d{4} = exactly 4 digits |
{n,m} | Between n and m times | \d{3,5} = 3-5 digits |
[abc] | Any of a, b, or c | [aeiou] = vowel |
[a-z] | Range: any lowercase letter | [A-Z] = uppercase |
^ | Start of string | ^Order = starts with "Order" |
$ | End of string | \.com$ = ends with ".com" |
(pattern) | Capture group | REGEXEXTRACT returns the group content |
Common recipes
Email: [\w.+-]+@[\w-]+\.[\w.-]+
US Phone: \(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}
URL: https?://[\w.-]+(?:/[\w./?=&%-]*)?
IP address: \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}
Date (ISO): \d{4}-\d{2}-\d{2}
Currency: \$\d+(?:,\d{3})*(?:\.\d{2})?
Parentheses around part of a pattern create a "capture group." REGEXEXTRACT returns just that captured portion, not the whole match. Example: =REGEXEXTRACT(A2, "Order: (\d+)") matches "Order: 12345" but returns just "12345". Perfect for pulling out one specific piece.
The "email regex" above works for typical emails but doesn't cover every valid RFC-5322 email address. Same for URLs, phone numbers, dates. For 95% of business data, common regex patterns are fine. For validation systems or strict compliance, you might need more complete patterns — or a proper validation library.
The verdict
🎯 Bottom line
Use LEFT/MID/RIGHT for known, fixed structures. Country codes, order prefixes, fixed-width IDs. Simpler to write, simpler to maintain, simpler to explain to teammates.
Reach for REGEXEXTRACT when the input varies. Emails, phone numbers, URLs, any pattern embedded in mixed content. The learning curve pays for itself the first time you'd otherwise need three nested FIND functions.
Learn both. Position functions handle daily cleanup; regex unlocks the messier cases. The combination of the two covers virtually every text extraction task you'll encounter.
Frequently asked questions
Should I use REGEXEXTRACT or LEFT/MID/RIGHT in Google Sheets?
Use LEFT/MID/RIGHT when the text has a fixed structure — same length, same position, predictable delimiters. Use REGEXEXTRACT when the text varies in shape but has a consistent pattern — emails, phone numbers, URLs, mixed content. Position-based functions are simpler when they work; regex handles the messy cases position functions can't.
Does Excel have REGEXEXTRACT?
Excel added REGEX functions in 2024 for Microsoft 365 (REGEXEXTRACT, REGEXTEST, REGEXREPLACE). Older Excel versions don't have them — you'd need LEFT/MID/RIGHT with FIND/SEARCH, or VBA. Google Sheets has had REGEXEXTRACT since inception, so it's more universally available in Sheets.
Is REGEXEXTRACT slower than LEFT/MID/RIGHT?
Marginally, yes. Regex engines add overhead per evaluation. On datasets under 10,000 rows, the difference is imperceptible. On 100,000+ rows, LEFT/MID/RIGHT can be noticeably faster. But performance shouldn't be the deciding factor — pick regex when the pattern is complex; pick position functions when it's simple.
What if the pattern doesn't match in REGEXEXTRACT?
REGEXEXTRACT returns #N/A when no match is found. Wrap with IFERROR to handle gracefully: =IFERROR(REGEXEXTRACT(A2, "pattern"), "no match"). This is similar to how FIND behavior interacts with LEFT/MID — you need to handle the missing-value case explicitly.
Can I extract multiple matches with REGEXEXTRACT?
REGEXEXTRACT returns the FIRST match by default. To get multiple matches, use REGEXEXTRACT within an ARRAYFORMULA context, or use SPLIT with a regex delimiter, or write custom Apps Script. For the common case of "get the first thing matching this pattern," REGEXEXTRACT is fine as-is.