Why Regex in Excel?

When text is messy—logs, emails, WhatsApp exports, bank statements—regular expressions (regex) give you superpowers to find and extract exactly what you need. Below are the 20 most useful real‑world patterns, each paired with a copy‑ready Excel/Sheets formula and a quick example.

 Excel note: If you’re on Google Sheets, REGEXEXTRACT works natively.
In Microsoft 365, you can create a one‑time LAMBDA named REGEXEXTRACT using Office Scripts or a community LAMBDA (or do the same in Power Query). I’ve included a quick setup at the end.

---

Quick Reference Matrix

---

???? Detailed Walkthrough (with Tips & Variations)

1) 6‑Digit OTP/PIN

Pattern: \b\d{6}\b
Why: Pins/OTPs are usually 6 digits in India.
Tip: For 4–8 digits, use \b\d{4,8}\b.

2) Email Address

Pattern: [\w\.\-]+@[\w\.\-]+
Tip: For stricter TLDs: [\w\.\-]+@[\w\.\-]+\.[A-Za-z]{2,}

3) Branded Invoice IDs (INV‑1234)

Pattern: INV-\d+
Variations: Replace INV- with your brand prefix (e.g., BILL-\d+).

4) Date in DD/MM/YYYY

Pattern: \b\d{2}/\d{2}/\d{4}\b
Normalize: Wrap with DATE(VALUE(RIGHT(x,4)),VALUE(MID(x,4,2)),VALUE(LEFT(x,2))).

5) Date in YYYY‑MM‑DD

Pattern: \b\d{4}-\d{2}-\d{2}\b
Why: ISO format—easy to sort.

6) Time HH:MM

Pattern: \b\d{2}:\d{2}\b
12‑hour support: \b(0?\d|1\d|2[0-3]):[0-5]\d\b

7) Indian Mobile (10 digits)

Pattern: \b\d{10}\b
With country code: (?:\+?91[-\s]?)?\b[6-9]\d{9}\b

8) Price with ₹ & Commas

Pattern: ₹?\s?\d{1,3}(?:,\d{3})*(?:\.\d{2})?
Rupee only: prefix ₹\s?
Note: Converts as text—use SUBSTITUTE to remove ₹/, before VALUE().

9) URL

Pattern: https?://[^\s>]+
Domain only: see #18.

10) Hashtag

Pattern: #\w+
Multiple: =TEXTJOIN(", ",TRUE,REGEXEXTRACTS(cell,"#\w+")) (custom function or Power Query).

11) Twitter/X Handle

Pattern: @\w+
Exclude email parts: use a negative lookbehind if needed: (?<!\w)@\w+.

12) Hex Color

Pattern: #?[0-9a-fA-F]{3,6}\b
Force full 6: #[0-9a-fA-F]{6}\b.

13) Capitalized Words

Pattern: \b[A-Z][a-z]+\b
All proper nouns in a sentence: run REGEXEXTRACTS (multi‑match) or use Power Query.

14) IPv4 Address

Pattern: \b(?:\d{1,3}\.){3}\d{1,3}\b
Strict 0–255: \b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b.

15) Indian PIN Code

Pattern: \b\d{6}\b
Starting 1–9 only: \b[1-9]\d{5}\b.

16) Alphabets Only

Pattern: \b[A-Za-z]+\b
Names incl. hyphen: \b[A-Za-z][A-Za-z\-]+\b.

17) Numbers Only

Pattern: \b\d+\b
Signed/decimal: [+-]?\d+(?:\.\d+)?.

18) Domain Name from URL

Pattern: (?<=://|^)([^/]+)
Subdomain‑free (root only): (?<=://|^)(?:www\.)?([^/:]+) then clean with LOWER().

19) Month‑Year (MM‑YYYY)

Pattern: \b\d{2}-\d{4}\b
To first day of month: =DATE(VALUE(RIGHT(x,4)),VALUE(LEFT(x,2)),1).

20) First Word of Sentence

Pattern: ^\w+
First token before comma/space: same, or use ^[^,\s]+.

---

One‑Time Setup: Using REGEXEXTRACT in Microsoft Excel

If you don’t have native regex functions:

Option A — Power Query (No code)

1. Data → Get Data → From Table/Range

2. Transform → Extract → Text Between Delimiters or use Column From Examples and let PQ infer a pattern.

3. For full regex, use Add Column → Custom with Text.RegexReplace/Text.RegexMatch in M (Power Query supports regex via .NET when invoked through custom functions or with add‑ins).

Option B — LAMBDA (Community Function)

You can add a ready LAMBDA named REGEXEXTRACT that wraps the new Office Scripts/VBA call to .NET regex or use a community‑shared pure‑formula version. After adding it once, all formulas above work the same as in Sheets.

If you want, tell me your Excel version and I’ll drop a plug‑and‑play LAMBDA or a tiny VBA UDF for REGEXEXTRACT, REGEXTEST, and REGEXREPLACE.

---

Best Practices & Pitfalls

• Anchor wisely: Use \b and ^…$ to avoid partial junk.

• Greedy vs lazy: Prefer +? when extracting minimal segments (e.g., URLs within quotes).

• Validate numeric ranges: For IPs/dates use stricter patterns where correctness matters.

• Clean before convert: Strip ₹, %, , before converting to numbers.

• Multiple matches: Excel needs a helper (Power Query / VBA / LAMBDA returning an array) for “all matches”.

---

FAQs

Q1. Will these work in Google Sheets?
Yes. Use them directly with REGEXEXTRACT.

Q2. What about multiple matches (e.g., many hashtags)?
Use Power Query, or a custom function that returns an array; then wrap with TEXTJOIN.

Q3. How do I replace instead of extract?
Use REGEXREPLACE(text, pattern, replacement) (Sheets) or an Excel UDF/LAMBDA.

Q4. Can I validate inputs (true/false)?
Yes—REGEXMATCH (Sheets) or a REGEXTEST UDF/LAMBDA in Excel.

---

Download / Next Steps

Want this as an Excel template with buttons to test each pattern and a Power Query version? Say “Send the Regex Template” and I’ll generate the files instantly.

---

Conclusion

With these 20 patterns you can clean, validate, and mine your data in seconds—emails, URLs, dates, money, IDs, social tags, and more. Bookmark the matrix above and start copy‑pasting into your sheets. Happy extracting!