Excel Text Functions: LEFT, MID, FIND, SUBSTITUTE & Clean Data

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Split Pinewick's order IDs into country and order number, measure every ID with LEN and count the IDs that have the wrong length.
• Pull the colour and the size out of Pinewick's marketplace titles, flag the linen products and count them, and learn why FIND and SEARCH can disagree.
• Migrate Pinewick's SKUs: switch the separator, count the parts, cut the last part after the LAST hyphen and rebrand the prefix by position.
• Clean Pinewick's newsletter sign-ups: proper names, lower-case emails, promo codes without any spaces, and a case-sensitive count of the names that were already clean.
• Build display names, one-line shipping addresses that skip empty units, customer codes and a list of all customers in one city for Pinewick's shipping labels.
• Turn Pinewick's invoice numbers into padded IDs, restore the lost leading zeros of ZIP codes and build readable labels with dates and dollar amounts.
• Split Pinewick's customer list into first name, last name (also with middle names), email domain and state, with formulas that update when the list changes.
• Parse the category out of Pinewick's order-line product codes (shop and outlet codes mixed), count lines and units per category and chart the result.