Excel TEXTSPLIT + REGEX Hybrid: Automatically Extract and Standardize Emails, Phone Numbers, and Paths

By tokenizing with TEXTSPLIT and using REGEX for precise extraction and validation, you can standardize emails, phone numbers, and paths from messy strings in 7 minutes. Paste the formulas below to reproduce the same results.

Quick Fix: Standardize in 7 Minutes

1. Convert the source data to a table with Ctrl+T (name it raw, with a Text column).
2. Preprocess with SUBSTITUTE(CHAR(160), " ") + TRIM + CLEAN.
3. Tokenize with TEXTSPLIT using a multiple-delimiter array.
4. Extract and validate emails, phone numbers, and paths with REGEXEXTRACT/REPLACE/TEST.
5. Complete the table with WRAPROWS + VSTACK/HSTACK.

How It Works

• TEXTSPLIT = large-scale splitting; REGEX = precise pattern extraction
• Email: [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}
• Phone digits only: [^0-9] / Includes regular expressions for Korean 10- and 11-digit formats
• Path extraction: (/[w-./?=&%]+)

Practical Example (Copy and Paste)

Preprocessing

=LET(
 s, raw[Text],
 t1, SUBSTITUTE(s,CHAR(160)," "),
 t2, TRIM(CLEAN(t1)),
 t2
)

Tokenization (TEXTSPLIT)

=LET(
 s, TRIM(CLEAN(raw[Text])),
 toks, TEXTSPLIT(s, {";","|",","}, CHAR(10), TRUE),
 TRIM(toks)
)

Extract Emails, Phone Numbers, and Paths

=LET(x,C2#,FILTER(x,REGEXTEST(x,"([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,})")))

=LET(x,C2#,nums,REGEXREPLACE(x,"[^0-9]",""),
 y,FILTER(nums,REGEXTEST(nums,"^d{10,13}$")),
 MAP(y,LAMBDA(n,IF(REGEXTEST(n,"^d{11}$"),
  REGEXREPLACE(n,"^(d{3})(d{4})(d{4})$","$1-$2-$3"),
  IF(REGEXTEST(n,"^d{10}$"),
  REGEXREPLACE(n,"^(d{3})(d{3})(d{4})$","$1-$2-$3"),n)))))

=LET(x,C2#,FILTER(x,REGEXTEST(x,"(/[w-./?=&%]+)")))

Domains

=LET(mails,D2#,MAP(mails,LAMBDA(m,IFERROR(REGEXEXTRACT(m,"@(.+)$"),""))))

Final Table (Email|Phone|Domain|Path)

=LET(mails,D2#,phones,E2#,paths,F2#,doms,G2#,
 tbl,HSTACK(mails,phones,doms,paths), WRAPROWS(tbl,4,""))

LAMBDA Function

=LAMBDA(rng,
 LET(
  s, TRIM(CLEAN(rng)),
  toks, TEXTSPLIT(s, {";","|",","}, CHAR(10), TRUE),
  t, TRIM(toks),
  mails, FILTER(t, REGEXTEST(t,"([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,})")),
  nums, REGEXREPLACE(t,"[^0-9]",""),
  phones0, FILTER(nums, REGEXTEST(nums,"^d{10,13}$")),
  phones, MAP(phones0,LAMBDA(n,IF(REGEXTEST(n,"^d{11}$"),
    REGEXREPLACE(n,"^(d{3})(d{4})(d{4})$","$1-$2-$3"),
    IF(REGEXTEST(n,"^d{10}$"),
      REGEXREPLACE(n,"^(d{3})(d{3})(d{4})$","$1-$2-$3"),n)))),
  paths, FILTER(t, REGEXTEST(t,"(/[w-./?=&%]+)")),
  doms, MAP(mails,LAMBDA(m,IFERROR(REGEXEXTRACT(m,"@(.+)$"),""))),
  tbl, HSTACK(mails,phones,doms,paths),
  WRAPROWS(tbl,4,"")
 )
)

Alternatives, Considerations, and Checklist

• For large datasets and combining disparate source data, Power Query is recommended.
• Clean the data first with Copilot Clean Data, then apply the hybrid approach.
• Replace nonbreaking spaces (160) with SUBSTITUTE before using TRIM.

Troubleshooting

Conclusion

You have now built an automation pipeline that converts long strings into a standardized table using Excel TEXTSPLIT and REGEX. The next article continues with duplicate removal and filtering by combining UNIQUE, FILTER, and SORT.