Splitting text at a character

Split emails at the @ and "Last, First" names at the comma, with FIND or the newer TEXTBEFORE and TEXTAFTER. Intermediate · 6 min · FIND, SEARCH, TEXTBEFORE, TEXTAFTER

LEFT, RIGHT and MID need to know where to cut. When the pieces vary in length, like the part of an email address before the @, you first have to find the cut point:

1. FIND("@", B2) returns the position of the @ in B2 2. LEFT(B2, position - 1) then takes everything before it

Newer versions of Excel add a shortcut: TEXTBEFORE and TEXTAFTER split at a delimiter in a single step. You will try both.

Steps

In C2:C8, find the position of the @ in each email address: =FIND("@", B2)
In D2:D8, take everything before the @ as the username. Use LEFT with the position from column C, minus 1 so the @ itself is left out.
In E2:E8, take everything after the @ as the domain. MID should start one character past the @. For the length, LEN(B2) is always enough, because MID stops at the end of the text.
Now the modern shortcut. The names in column A are written Last, First. In F2:F8, get each first name with =TEXTAFTER(A2, ", "), which returns everything after the comma and space.
In G2:G8, get each last name with TEXTBEFORE: everything before the comma.

Remember

FIND (case-sensitive) and SEARCH (not case-sensitive) return a character's position.
Combine that position with LEFT or MID to split text of any length.
TEXTBEFORE and TEXTAFTER split at a delimiter in one step.
Mind the spaces: split at ", " rather than "," when a space follows the comma.

Previous: LEFT, RIGHT, MID and LEN · Next: Cleaning up messy text