Unit 10 of 12 Lesson 1 free · Plus

Data cleanup

Fix messy exports before they bite.

Key ideas

=SUBSTITUTE(A2,"-","")

Swap out characters everywhere they appear, like dashes in phone numbers.

=TEXTBEFORE(A2,"@")

TEXTBEFORE and TEXTAFTER grab text on either side of a character.

=COUNTIF($A$2:A2,A2)>1

An expanding range counts how many times a value has appeared so far.

Strip the dashes out of the phone number in B2. Fill to B4.
A B
1 Phone Digits
2 816-555-0142
3 913-555-0199
4 785-555-0110
Hint: =SUBSTITUTE(A2,"-","")
Show answer
=SUBSTITUTE(A2,"-","") in B2. Replacing with "" deletes the character.
Pull the username (before the @) from each email into B2. Fill to B4.
A B
1 Email User
2 ava.chen@acme.com
3 ben@northwind.io
4 cara.diaz@acme.com
Hint: =TEXTBEFORE(A2,"@")
Show answer
=TEXTBEFORE(A2,"@") in B2. No more counting characters with LEFT and FIND.
Pull the company domain (after the @) into B2. Fill to B4.
A B
1 Email Domain
2 ava.chen@acme.com
3 ben@northwind.io
4 cara.diaz@acme.com
Hint: =TEXTAFTER(A2,"@")
Show answer
=TEXTAFTER(A2,"@") in B2. Great for grouping leads by company.
What will A1 show?
A
1
A1 contains =SUBSTITUTE("Q1 report Q1","Q1","Q2",2)
Show answer
Q1 report Q2. The 4th argument replaces only the 2nd match.
Match each function to what it does
SUBSTITUTE
Swaps text for other text
TEXTBEFORE
Text left of a character
TEXTAFTER
Text right of a character
VALUE
Turns text into a number

	A
1

Lesson 2 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.

Lesson 3 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.