Working with text is a core Excel skill for data cleaning, analytics, and reporting. These functions help you extract, clean, measure, and combine text quickly.
Example Table
| First Name | Last Name | Address | Phone | Date |
|---|---|---|---|---|
| John | Doe | 123 Main St | 555-123-4567 | 01/15/2024 |
| Jane | Smith | 456 Oak Ave | 555-987-6543 | 12/05/2023 |
Assume this data starts in row 2 (A2:E3).
LEFT, RIGHT, MID — Extract Characters
| Function | Purpose | Example | Result |
|---|---|---|---|
LEFT(text, num_chars) | First characters | =LEFT(D2,3) | 555 (area code) |
RIGHT(text, num_chars) | Last characters | =RIGHT(D2,4) | 4567 |
MID(text, start, num_chars) | Middle characters | =MID(TEXT(E2,"MM/DD/YYYY"),1,2) | 01 (month) |
Use these when parts of a cell contain meaningful pieces of data.
TRIM — Remove Extra Spaces
Extra spaces break formulas and comparisons.
If a cell contains:
" John "Use:
=TRIM(A2)Removes leading and trailing spaces.
Important: If you delete the original column, the TRIM result breaks.
Use Paste Special → Values to make it permanent.
LEN — Count Characters
Counts all characters, including spaces and symbols.
=LEN(A2) → 4=LEN(C2) → counts full address lengthUseful for:
- Validating data length
- Preparing fixed-width formats
- Finding hidden spaces
CONCAT and & — Combine Text
Using &
=A2 & " " & B2→ John Doe
Using CONCAT
=CONCAT(A2," ",B2)→ John Doe
Both work the same. CONCAT is newer and handles ranges well.
Key Takeaways
- LEFT / RIGHT / MID → extract parts of text
- TRIM → remove extra spaces that cause errors
- LEN → count characters
- CONCAT / & → combine text
- Use Paste Special → Values when you want results to stay permanent
Joshua Bowen's Notes 