Split and clean text: TEXTSPLIT and REGEX
Split a record into columns with TEXTSPLIT, keep only the digits of a phone number, check email addresses, and pull an invoice number out of a note. Advanced · 7 min · TEXTSPLIT, REGEXREPLACE, REGEXTEST, REGEXEXTRACT, Regular expressions
Text pasted from forms and emails rarely arrives in tidy columns. Two kinds of tool deal with it:
TEXTSPLIT(text, separator)cuts text wherever the separator appears and spills the pieces- The REGEX functions work with patterns (regular expressions) rather than exact text. A few pieces go a long way:
\dis any digit,\Danything that is not a digit,+means "one or more",^and$mean the start and end of the text.
The sheet holds sign-ups copied out of a web form.
Steps
The sign-up in
A2holds a name, an email and a phone number, separated by a comma and a space. InC2, split it into three cells:=TEXTSPLIT(A2, ", ")The phone number in
E2has brackets, a space and a dash. InC4, replace everything that is not a digit with nothing:=REGEXREPLACE(E2, "\D", "")Which emails in
G2:G6look valid? A rough pattern: some text, an @, more text, a dot, then letters. InH2:=REGEXTEST(G2:G6, "^\S+@\S+\.[a-z]+quot;)The note in
A9mentions an invoice somewhere in the middle. InC11, pull out "INV-" followed by its digits:=REGEXEXTRACT(A9, "INV-\d+")
Remember
TEXTSPLIT(text, ", ")splits text at a separator and spills the pieces.REGEXREPLACE(text, "\D", "")keeps only the digits.REGEXTEST(text, pattern)checks text against a pattern;^and$make it match the whole text.REGEXEXTRACT(text, "INV-\d+")pulls out the first part that matches.- REGEX functions are in Excel for Microsoft 365. TEXTSPLIT is in Microsoft 365 and Excel 2024.
In the function reference: REGEXEXTRACT, REGEXREPLACE, REGEXTEST, TEXTSPLIT
Previous: Formatting numbers in text with TEXT · Next: How Excel stores dates