Text › TEXTBEFORE, TEXTAFTER52 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeTEXTBEFORETEXTAFTER

TEXTBEFORE & TEXTAFTER: split email addresses

The committee of a career webinar in Jakarta collected registrants' email addresses. Nadia wants to split the username (before the @) from the domain (after the @) to see how many people used a campus email.

Task

Fill B2:B6 with the username (the text before @) and C2:C6 with the domain (the text after @) of the email in column A. Use TEXTBEFORE and TEXTAFTER.

Cells to fillB2:C6

Required functionsTEXTBEFORETEXTAFTER

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
TEXTBEFORE(text, delimiter) returns everything before the delimiter, while TEXTAFTER returns everything after it.
Hint 2
The delimiter is the @ sign, written as text in quotes.
Hint 3
Column B uses TEXTBEFORE and column C uses TEXTAFTER, both with cell A2 and the same delimiter.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

B2:B6

=TEXTBEFORE(A2,"@")

Write it in B2, then fill down to B6.

C2:C6

=TEXTAFTER(A2,"@")

Write it in C2, then fill down to C6.

Explanation

TEXTBEFORE and TEXTAFTER (Excel 365 and Excel 2024) are much shorter than the old way, =LEFT(A2,FIND("@",A2)-1) and =MID(A2,FIND("@",A2)+1,100). When the delimiter appears several times, the third argument picks which occurrence; a negative number counts from the end, for example TEXTAFTER(A2,".",-1) returns the part after the last dot. If the delimiter is not found, the result is #N/A.

Starting worksheet: Sheet1
Row	A	B	C
1	Email	Username	Domain
2	rina.putri@gmail.com
3	dimas_08@student.univ.ac.id
4	ayu.lestari@yahoo.co.id
5	fajar.nugroho@outlook.com
6	nadia.k@mail.kampus.ac.id
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result