Course / Working with Text

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:

The sheet holds sign-ups copied out of a web form.

Steps

  1. The sign-up in A2 holds a name, an email and a phone number, separated by a comma and a space. In C2, split it into three cells: =TEXTSPLIT(A2, ", ")

  2. The phone number in E2 has brackets, a space and a dash. In C4, replace everything that is not a digit with nothing: =REGEXREPLACE(E2, "\D", "")

  3. Which emails in G2:G6 look valid? A rough pattern: some text, an @, more text, a dot, then letters. In H2: =REGEXTEST(G2:G6, "^\S+@\S+\.[a-z]+

    quot;)

  4. The note in A9 mentions an invoice somewhere in the middle. In C11, pull out "INV-" followed by its digits: =REGEXEXTRACT(A9, "INV-\d+")

Remember

In the function reference: REGEXEXTRACT, REGEXREPLACE, REGEXTEST, TEXTSPLIT

Previous: Formatting numbers in text with TEXT · Next: How Excel stores dates