Level 1 · Split emails with LEFT, FIND and RIGHT

A column of email addresses holds two facts in one string: who the person is, and where they work. For business customers the part after the @ is the company, which is exactly what Deborah needs to group buyers into accounts. Spreadsheets have no “split at the @” button that survives new rows, so you combine a few small text functions.

Counting characters: LEFT, RIGHT and LEN

Text functions count characters from the start of the string, starting at 1.

Function	Returns	On D2 (hbrooks@lakesidecafe.com)
LEN(text)	how many characters	LEN(D2) is 24
LEFT(text, n)	the first n characters	LEFT(D2,7) is hbrooks
RIGHT(text, n)	the last n characters	RIGHT(D2,16) is lakesidecafe.com
MID(text, start, n)	n characters from position start	MID(D2,9,12) is lakesidecafe

Typing 7 and 16 works for row 2 only. D3 is bgraham@bluecedarhomes.com, where the @ sits in a different place. The counts have to come from the text itself.

FIND the delimiter

FIND(what, text) returns the position of the first match:

=FIND("@",D2)

In D2 that is 8: seven letters of hbrooks, then the @. Everything before position 8 is the user, so the user is 8 − 1 = 7 characters long:

E2   =LEFT(D2,FIND("@",D2)-1)

The domain is what is left after the @. The whole address is 24 characters, and 8 of them run up to and including the @, so the domain is the last 24 − 8 = 16:

F2   =RIGHT(D2,LEN(D2)-FIND("@",D2))

MID does the same job: =MID(D2,FIND("@",D2)+1,LEN(D2)) starts one character after the @ and asks for more characters than remain, which simply returns the rest. Use whichever reads better to you. Fill both formulas down to row 25.

Check the result

Scan the domain column. Row 4 is foxglovedesign.co, one letter shorter than a .com, and it should still be complete; a typed count would have cut it or dragged in the @. Then look for repeats: oakandashinteriors.com shows up four times in this slice and bluecedarhomes.com three times. Those are the offices Deborah would have emailed more than once.

A quick check on any split: the pieces plus the delimiter should rebuild the original. =E2&"@"&F2=D2 returns TRUE on every row when nothing was lost.

TEXTBEFORE and TEXTAFTER

Excel 365 has two functions that do the split in one step:

=TEXTBEFORE(D2,"@")
=TEXTAFTER(D2,"@")

They are easier to read, and they take an instance number for strings with several delimiters (TEXTAFTER(D2,".",-1) returns the last part, com). Older Excel versions don’t have them, so a sheet you send to a client may show #NAME?. LEFT with FIND works in every version and in Google Sheets, which is why this ticket asks for it. Learn both, and pick by who opens the file.

Text to Columns: the one-off split

Sometimes a whole import lands in one column. A CSV opened the wrong way gives you rows like Hazel Linen Throw Pillow,2,20.99: product, quantity and price glued together by commas. You don’t need formulas for that. Select the column, then Data → Text to Columns, choose Delimited, tick Comma, and Excel writes each piece into its own column (check that the columns to the right are empty first, or it overwrites them). In Google Sheets it is Data → Split text to columns. Importing the file through Data → From Text/CSV avoids the problem in the first place, because Excel detects the delimiter for you.

The difference from this level: Text to Columns is a one-time action that writes values. When next week’s export arrives, you do it again by hand. The formulas you just wrote recalculate on any new row you paste in, so use them for anything you’ll repeat.

Common mistakes

• Keeping the delimiter. =LEFT(D2,FIND("@",D2)) returns hbrooks@. The position of the @ counts the @ itself, hence the -1.
• FIND vs SEARCH. FIND is case-sensitive and SEARCH is not: FIND("A",D2) fails on a lowercase address. For a symbol like @ they behave the same.
• No delimiter at all. On text without an @, FIND returns #VALUE!. A real export usually has a few of those; chapter 5 deals with them.
• Hard-coded lengths. RIGHT(D2,16) happens to be right for row 2 and wrong almost everywhere else.