Cellwise

Unit 6 of 12

Working with text

Clean, join, and slice words.

Functions in this unit: PROPER, TRIM, UPPER, LOWER, LEFT, RIGHT, LEN, FIND

Key ideas

Clean messy names

=PROPER(TRIM(A2))

TRIM removes extra spaces. PROPER capitalizes each word.

Join with &

=A2&" "&B2

The & glues text together. Add spaces and punctuation in quotes.

Slice with LEFT, RIGHT, MID

=LEFT(A2,2)

Grab characters from the start, the end, or the middle. LEN tells you the length.

Lesson 1: Cleaning text

  1. Capitalize the name properly in B2.

    AB
    1Raw nameClean
    2jordan lee
    3MARIA GARCIA
    4sam o'brien

    Hint: =PROPER(A2)

    Show answer

    =PROPER(A2) in B2. PROPER even handles O'Brien correctly.

  2. Remove the extra spaces from the name in B2.

    AB
    1Raw nameClean
    2 Jordan Lee
    3Maria Garcia
    4 Sam O'Brien

    Hint: =TRIM(A2)

    Show answer

    =TRIM(A2) in B2. TRIM strips spaces at the ends and squeezes double spaces inside.

  3. What will B1 show?

    AB
    1kc royals

    B1 contains =UPPER(A1)

    Show answer

    KC ROYALS. UPPER converts every letter to a capital.

  4. Match each function to what it does

    TRIM
    Removes extra spaces
    PROPER
    Capitalizes each word
    UPPER
    ALL CAPS
    LOWER
    all lowercase

  5. These names are messy in two ways. Clean both problems in B2 with one formula.

    AB
    1Raw nameClean
    2 jordan LEE
    3maria garcia
    4 SAM o'brien

    Hint: Wrap one function inside the other: =PROPER(TRIM(A2))

    Show answer

    =PROPER(TRIM(A2)) in B2. Nesting functions lets you fix several problems at once.

Lesson 2: Joining text

  1. Make a full name in C2 from First and Last, with a space between.

    ABC
    1FirstLastFull name
    2AvaChen
    3BenOrtiz
    4CaraDiaz

    Hint: =A2&" "&B2

    Show answer

    =A2&" "&B2 in C2. The " " in the middle is a space stored as text.

  2. Build "Last, First" for row 2

    Show answer

    =B2&", "&A2. Use & to join, never +. A plus sign tries to do math.

  3. What will A1 show?

    A
    1

    A1 contains ="Q"&3

    Show answer

    Q3. & turns the number into text and joins it.

  4. What happens with ="Hi"+"There"?

    • #VALUE! error
    • HiThere
    • Hi There
    • 0
    Show answer

    #VALUE! error. + is for numbers. Use & to join text.

  5. Build a company email in C2: first.last@acme.com in lowercase.

    ABC
    1FirstLastEmail
    2AvaChen
    3BenOrtiz
    4CaraDiaz

    Hint: =LOWER(A2&"."&B2)&"@acme.com"

    Show answer

    =LOWER(A2&"."&B2)&"@acme.com" in C2. Combining LOWER with & is how teams generate usernames in bulk.

Lesson 3: Slicing text

  1. Pull the 2-letter location prefix from the code into B2.

    AB
    1CodeLocation
    2KC-2041
    3NY-1187
    4LA-3302

    Hint: =LEFT(A2,2)

    Show answer

    =LEFT(A2,2) in B2. LEFT grabs characters from the start.

  2. Pull the 4-digit number from the end of the code into B2.

    AB
    1CodeNumber
    2KC-2041
    3NY-1187
    4LA-3302

    Hint: =RIGHT(A2,4)

    Show answer

    =RIGHT(A2,4) in B2. RIGHT grabs characters from the end.

  3. What will B1 show?

    AB
    1Excel

    B1 contains =LEN(A1)

    Show answer

    6. The trailing space counts as a character.

  4. Get just the first name in B2. It is everything before the space.

    AB
    1Full nameFirst
    2Ava Chen
    3Benjamin Ortiz
    4Cara Diaz

    Hint: FIND(" ",A2) gives the space position. Take one less than that with LEFT.

    Show answer

    =LEFT(A2,FIND(" ",A2)-1) in B2. FIND makes your slicing adapt to names of any length.

  5. What does =MID("KC-2041",4,2) return?

    • 20
    • -2
    • C-
    • 41
    Show answer

    20. Start at character 4 and take 2 characters.