How to extract text before or after a character
Grid by Zaweba has TEXTBEFORE and TEXTAFTER to pull out the part of a cell before or after a character, such as a domain from an email address.
TEXTBEFORE and TEXTAFTER are the functions for it. In Grid by Zaweba, =TEXTBEFORE(A2,"@") returns everything before the @ in an email address, and =TEXTAFTER(A2,"@") returns the domain after it. Both work with any character or string you name, and they can count from the start or the end of the text.
How the two functions work
Each takes the text to look in and the delimiter to look for, which can be one character or several. TEXTBEFORE returns what comes before the delimiter; TEXTAFTER returns what comes after it. If the delimiter isn't in the text at all, the result is an error, so it pays to plan for that.
An optional instance number chooses which occurrence to use. The default is the first. Ask for the second and you get the text before or after the second occurrence. A negative number counts from the end, which is the trick for anything that should use the last occurrence, such as a surname after the last space or a file extension after the last full stop.
Worked examples
Email addresses: TEXTBEFORE with "@" gives the user name, TEXTAFTER with "@" gives the domain. A quick UNIQUE on the domain column then shows which organisations your contacts come from.
Names: TEXTBEFORE with a space gives the first name. TEXTAFTER with a space and an instance of minus one gives whatever follows the last space, which copes with middle names.
File paths: TEXTAFTER with a backslash and an instance of minus one returns the file name from a full path. TEXTAFTER on the file name with a full stop and minus one gives the extension.
Codes: a product code like SHOE-0042-BLK splits naturally on the hyphen. TEXTBEFORE gives the category, and TEXTAFTER on the second hyphen gives the colour.
Handling missing delimiters
Real data is never quite consistent. An email column may hold a blank or a phone number by mistake, and a name may be a single word. TEXTBEFORE and TEXTAFTER have an optional argument for what to return when the delimiter isn't found, so you can show the whole text or a blank instead of an error. Wrapping the formula in IFERROR does the same job if you'd rather keep the formula short.
Case can matter too. The functions have an option to match without caring about capitals, useful when the delimiter is a word such as " and " that might be typed as " AND ".
When you need something more flexible
For patterns rather than fixed characters, the REGEX functions in Grid go further. REGEXEXTRACT can pull out the first run of digits in a cell, or a postcode wherever it sits. TEXTSPLIT is the right tool when you want every part, not just one, since it spills all the pieces into neighbouring cells. And MID with FIND, the old way of doing this, still works in sheets that already use it.
If you're typing these formulas for the first time, Grid's autocomplete shows each argument in bold as you go, and Insert Function (fx) lists them under Text with a description of every argument and a live result as you fill them in.
Tidy results
Text pulled out of a longer string often keeps a stray space at one end. TRIM around the result fixes it. And if the part you've extracted is a number, such as an order number after a hash sign, VALUE will turn it from text into a number you can sort and add.
These two functions replace a lot of fiddly FIND and MID arithmetic, which is why they're worth learning. In Grid by Zaweba they're part of a Text family of 47 functions, on your own PC with no account.
