luvv to helpDiscover the Best Free Online Tools
Topic 10 of 14

Text Functions

Learn Text Functions for free with explanations, exercises, and a quick test (for Data Analyst).

Published: December 20, 2025 | Updated: December 20, 2025

Table of Contents

Why this matters

Data rarely arrives clean. As a Data Analyst, you will standardize names, generate emails, split product codes, extract IDs from URLs, and clean survey text. Text functions help you do this fast and reproducibly without manual edits.

  • Marketing lists: Clean names, fix casing, and create first.last@company.com.
  • Operations: Split SKUs into category, item number, and color.
  • Web analytics: Extract campaign IDs from long URLs.
  • Finance: Normalize vendor names and remove extra spaces.

Concept explained simply

Text functions treat each cell as a string (a sequence of characters). You can count characters, grab parts, find positions, replace characters, and change case. Combine them to build powerful cleaning pipelines.

Mental model

  • Locate first, then extract: Use FIND/SEARCH to get positions. Then use LEFT/MID/RIGHT.
  • Clean before compare: TRIM + CLEAN + consistent case (LOWER/UPPER/PROPER).
  • Replace, then split: Use SUBSTITUTE or SPLIT when possible; fall back to MID with positions.
  • Join smartly: TEXTJOIN can ignore blanks; & or CONCAT is simple for two items.
Commonly used functions (quick reference)
  • Clean/case: TRIM, CLEAN, UPPER, LOWER, PROPER
  • Find/length: FIND (case-sensitive), SEARCH (case-insensitive), LEN
  • Extract: LEFT, RIGHT, MID
  • Replace/split/join: SUBSTITUTE, REPLACE, SPLIT (Sheets), TEXTSPLIT (new Excel), TEXTJOIN, CONCAT, &
  • Format/convert: TEXT, VALUE

Worked examples

Example 1: Clean a full name

Cell A2: " mARY annE o'NEIL "

  1. Remove extra spaces: TRIM(A2)
  2. Fix non-printables (if any): CLEAN(TRIM(A2))
  3. Proper case: PROPER(CLEAN(TRIM(A2))) → Mary Anne O'Neil

Mental check: Use LEN before and after cleaning to confirm space reduction.

Example 2: Extract domain from an email

Cell B2: alex.cho@contoso-analytics.com

  1. Position of @: FIND("@",B2)
  2. Domain: MID(B2, FIND("@",B2)+1, LEN(B2)-FIND("@",B2)) → contoso-analytics.com

Note: Use SEARCH instead of FIND if case-insensitivity matters.

Example 3: Split SKU parts

Cell C2: CAT-0142-BLUE

  • Category: LEFT(C2, FIND("-",C2)-1) → CAT
  • Item number: MID(C2, FIND("-",C2)+1, FIND("-",C2, FIND("-",C2)+1) - FIND("-",C2) - 1) → 0142
  • Color: RIGHT(C2, LEN(C2) - FIND("-",C2, FIND("-",C2)+1)) → BLUE

Tip: The third argument in FIND lets you search from a position to locate the second hyphen.

Who this is for

  • Aspiring and junior Data Analysts who clean datasets in Excel or Google Sheets.
  • Anyone preparing datasets for BI tools or basic modeling.

Prerequisites

  • Basic spreadsheet navigation and formulas (= to start, referencing cells).
  • Comfort with relative/absolute references (e.g., A2 vs $A$2).

Exercises and practice tasks

Try these hands-on tasks. Use the hints if you get stuck. Solutions are collapsible.

Exercise 1: Clean and parse product codes

Set up a sheet with columns: A: Raw_SKU. Sample rows:

  • cat-001 blue
  • CAT-002-green
  • Cat-003 RED

Goals:

  • In B: Clean_SKU → remove extra spaces and standardize to uppercase hyphenated form (e.g., CAT-001-BLUE).
  • In C: Category → text before first -.
  • In D: Number → middle part (3 digits).
  • In E: Color → last part, uppercase.
Hints
  • Start with TRIM and UPPER.
  • Use SUBSTITUTE to normalize separators (spaces to hyphens).
  • Find hyphen positions with FIND and extract via LEFT/MID/RIGHT.
Show solution (summary)

In B2: UPPER(SUBSTITUTE(TRIM(SUBSTITUTE(A2," ","-")),"--","-")) (may add nested SUBSTITUTE to collapse multiple dashes)

In C2: LEFT(B2, FIND("-",B2)-1)

In D2: MID(B2, FIND("-",B2)+1, FIND("-",B2, FIND("-",B2)+1) - FIND("-",B2) - 1)

In E2: RIGHT(B2, LEN(B2) - FIND("-",B2, FIND("-",B2)+1))

Exercise 2: Generate emails from names

Columns: A: First, B: Last. Example: " Ana-Maria " and " O'Connor "

Goal: In C: Email → first.last@yourco.com all lowercase, remove spaces/apostrophes/hyphens in the email parts.

Hints
  • Clean names with TRIM.
  • Lowercase with LOWER.
  • Strip punctuation via nested SUBSTITUTE.
  • Join with TEXTJOIN or &.
Show solution (summary)

Helper for first (optional in D2): LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"'",""),"-","")," ",""))

Helper for last (optional in E2): same approach on B2.

All-in-one (in C2): LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"'",""),"-","")," ","")) & "." & LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"'",""),"-","")," ","")) & "@yourco.com"

Self-check checklist

  • LEN decreased after cleaning when expected.
  • No double separators (e.g., -- or ..).
  • Case is consistent (all lower or proper case).
  • Extraction formulas still work if cell content slightly varies (try 1–2 variations).

Common mistakes and how to self-check

  • Using FIND when case-insensitivity is needed. Fix: Use SEARCH.
  • Forgetting to TRIM before comparisons. Fix: Wrap inputs with TRIM and, if needed, CLEAN.
  • Hardcoding positions in MID. Fix: Compute positions with FIND/SEARCH so formulas generalize.
  • Nesting many SUBSTITUTE calls without order. Fix: Remove broader issues first (spaces), then specific punctuation.
  • Joining text that includes blanks. Fix: Use TEXTJOIN with ignore_empty=TRUE (where available).

Practical projects

  • Customer list cleanup: Standardize name casing, remove extra spaces, and generate emails. Deliver a cleaned sheet and a summary of rules used.
  • SKU dictionary: Parse 200+ SKUs into components (category, model, color). Validate with a pivot table of categories and colors.
  • UTM parser: From a list of URLs, extract source, medium, and campaign into separate columns using FIND/MID or SPLIT/TEXTSPLIT.

Learning path

1) Clean basics
Practice TRIM, CLEAN, PROPER, LOWER, UPPER.
2) Locate and extract
Use FIND/SEARCH with LEN, then LEFT/MID/RIGHT.
3) Replace and split
Apply SUBSTITUTE, REPLACE, SPLIT/TEXTSPLIT.
4) Join and format
Use TEXTJOIN, CONCAT, &, and TEXT/VALUE for conversions.
5) Automate
Build reusable cleaning formulas and test on new data.

Mini challenge

You receive a column Campaign with values like Q1_2025-SEM-Google_US and Q2_2025-Email-Newsletter_UK. Create formulas that output:

  • Quarter (e.g., Q1_2025)
  • Channel (e.g., SEM or Email)
  • Source (e.g., Google or Newsletter)
  • Country (e.g., US or UK)

Constraint: Assume separators are underscores and hyphens as shown, but lengths vary. Aim to use FIND/SEARCH + LEFT/MID/RIGHT so it generalizes.

One possible approach (peek)

Quarter: up to first - → LEFT(A2, FIND("-",A2)-1)
Country: after last _ → if available, RIGHT(A2, LEN(A2)-FIND("_",A2, FIND("_",A2)+1)) or use a helper to find the last underscore.

Next steps

  • Apply these functions to a real dataset you own (contacts, products, or logs).
  • Document your cleaning rules in a separate sheet tab so others can reuse them.
  • Take the quick test to check retention. The test is available to everyone; only logged-in users get saved progress.

Practice Exercises

2 exercises to complete

Instructions

Create a sheet with column A labeled Raw_SKU and add:

  • cat-001 blue
  • CAT-002-green
  • Cat-003 RED

Tasks:

  • Column B (Clean_SKU): Normalize to uppercase and a single hyphen separator (e.g., CAT-001-BLUE).
  • Column C (Category): Text before first hyphen.
  • Column D (Number): Middle 3-digit number.
  • Column E (Color): Final part, uppercase.
Expected Output
Rows produce: B=CAT-001-BLUE / CAT-002-GREEN / CAT-003-RED. C=CAT, D=001/002/003, E=BLUE/GREEN/RED.

Text Functions — Quick Test

Test your knowledge with 8 questions. Pass with 70% or higher.

8 questions70% to pass
Charts in SheetsDate Functions

Have questions about Text Functions?

AI Assistant

Ask questions about this tool