Split Email Addresses into Username and Domain
Your task: In B2:C2, split each email address into its username and domain, then fill them down.
Full instructions, scenario and goal
Scenario
The marketing team exported a list of customer email addresses and wants to segment it by company. They need the part before the @ sign (the username) and the part after it (the domain) in separate columns. The addresses are all different lengths, and the list will be pasted over with a new export next month.
Your task
- Look at the email addresses in A2:A6.
- In B2, write a formula that returns the username: everything before the @ sign.
- In C2, write a formula that returns the domain: everything after the @ sign.
- Copy B2:C2 and paste them over B3:C6.
- Select Check Answer.
You are done when
- B2:C6 contain formulas that give the username and the domain of the address on the same row.
- The @ sign itself is in neither result.
- The formulas work for addresses of any length, so they still work after the next export.
What you will practise
Extract part of a text by finding a separator, instead of counting characters by hand.
The idea in 30 seconds
Excel's text functions cut pieces out of text by position. LEFT returns a number of characters from the start, RIGHT from the end, and MID starts at any position and returns the number of characters you ask for. LEN returns the total length of a text, and FIND returns the position of a character or word inside another text, counting from 1.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell B2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Excel's text functions cut pieces out of text by position. LEFT returns a number of characters from the start, RIGHT from the end, and MID starts at any position and returns the number of characters you ask for. LEN returns the total length of a text, and FIND returns the position of a character or word inside another text, counting from 1.
Real data rarely has the same length in every row, so counting characters only works for the row you looked at. The reliable pattern is to find the separator first, then use its position to work out how much to take. Remember that a position counts the separator itself, so the text before it is one character shorter and the text after it starts one character later.
Skills you will practise
- FIND
- LEFT
- MID
- RIGHT
- LEN
- Text extraction
Topic guide: Excel Text Functions Practice: TRIM, LEFT, TEXT and More
Common mistakes to avoid
- Keeping the @ sign
- The position returned by FIND counts the @ sign. Take one less for the username, and start one later for the domain.
- Counting characters by hand
- A formula such as LEFT(A2,11) works for the first address and cuts every other address in the wrong place.