Cellwise

Unit 10 of 12

Data cleanup

Fix messy exports before they bite.

Functions in this unit: SUBSTITUTE, TEXTBEFORE, TEXTAFTER, ISNUMBER, VALUE, IF, ISBLANK, ISTEXT, COUNTIF, COUNTA, UNIQUE

Key ideas

Replace text

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

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

Split on a delimiter

=TEXTBEFORE(A2,"@")

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

Find duplicates

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

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

Lesson 1: Find and replace

  1. Strip the dashes out of the phone number in B2. Fill to B4.

    AB
    1PhoneDigits
    2816-555-0142
    3913-555-0199
    4785-555-0110

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

    Show answer

    =SUBSTITUTE(A2,"-","") in B2. Replacing with "" deletes the character.

  2. Pull the username (before the @) from each email into B2. Fill to B4.

    AB
    1EmailUser
    2ava.chen@acme.com
    3ben@northwind.io
    4cara.diaz@acme.com

    Hint: =TEXTBEFORE(A2,"@")

    Show answer

    =TEXTBEFORE(A2,"@") in B2. No more counting characters with LEFT and FIND.

  3. Pull the company domain (after the @) into B2. Fill to B4.

    AB
    1EmailDomain
    2ava.chen@acme.com
    3ben@northwind.io
    4cara.diaz@acme.com

    Hint: =TEXTAFTER(A2,"@")

    Show answer

    =TEXTAFTER(A2,"@") in B2. Great for grouping leads by company.

  4. 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.

  5. 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

Lesson 2: Check your data

  1. Some amounts were imported as text. Check whether each is a real number in C2. Fill to C5.

    ABC
    1InvoiceAmountIs number?
    2INV-1120
    3INV-285
    4INV-342.5
    5INV-419.99

    Hint: =ISNUMBER(B2)

    Show answer

    =ISNUMBER(B2) in C2. Text that looks like a number will quietly break your totals.

  2. Convert the text amounts into real numbers in C2. Fill to C4.

    ABC
    1InvoiceAmount (text)Amount
    2INV-1120
    3INV-285.5
    4INV-342

    Hint: =VALUE(B2)

    Show answer

    =VALUE(B2) in C2. Now SUM and AVERAGE will see them.

  3. Flag rows with no email as "Missing", otherwise "OK". Start in C2, fill to C5.

    ABC
    1NameEmailStatus
    2Avaava@acme.com
    3Ben
    4Caracara@acme.com
    5Dev

    Hint: =IF(ISBLANK(B2),"Missing","OK")

    Show answer

    =IF(ISBLANK(B2),"Missing","OK") in C2. ISBLANK is TRUE only for truly empty cells.

  4. Why do numbers stored as text cause problems?

    • SUM and AVERAGE skip them
    • They cannot be displayed
    • They make the file larger
    • They turn into dates
    Show answer

    SUM and AVERAGE skip them. Your total looks fine but is quietly too low.

  5. What will A1 show?

    A
    1

    A1 contains =ISTEXT(42)

    Show answer

    FALSE. 42 is a number, not text.

Lesson 3: Duplicates and uniques

  1. Count how many times each email appears in the list. Start in B2, fill to B7.

    AB
    1EmailCount
    2ava@acme.com
    3ben@acme.com
    4ava@acme.com
    5cara@acme.com
    6ben@acme.com
    7ava@acme.com

    Hint: Lock the list: =COUNTIF($A$2:$A$7,A2)

    Show answer

    =COUNTIF($A$2:$A$7,A2) in B2. Anything above 1 is a duplicate.

  2. Mark only the repeat appearances as "Duplicate" and leave first appearances blank. Start in B2, fill to B7.

    AB
    1EmailFlag
    2ava@acme.com
    3ben@acme.com
    4ava@acme.com
    5cara@acme.com
    6ben@acme.com
    7ava@acme.com

    Hint: Lock only the start of the range so it grows: $A$2:A2

    Show answer

    =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","") in B2. The range grows one row at a time, so it only counts what came before.

  3. Why does $A$2:A2 grow when filled down?

    • The start is locked but the end moves
    • Both ends are locked
    • Neither end moves
    • Excel adds rows automatically
    Show answer

    The start is locked but the end moves. In row 5 it becomes $A$2:A5.

  4. How many different emails are there? Put the count in D1.

    ABCD
    1EmailUnique
    2ava@acme.com
    3ben@acme.com
    4ava@acme.com
    5cara@acme.com
    6ben@acme.com
    7ava@acme.com

    Hint: =COUNTA(UNIQUE(A2:A7))

    Show answer

    =COUNTA(UNIQUE(A2:A7)) in D1. UNIQUE removes repeats, COUNTA counts what is left.

  5. Build: a list of each email once

    Show answer

    =UNIQUE(A2:A7). UNIQUE spills the results down automatically.