Why Text Functions Matter
Real-world data is messy — names have extra spaces, dates are stored as text strings, product codes are buried inside longer strings, and phone numbers need formatting. Text functions let you clean and transform this data without manually editing each cell.
Sample Data
A B C
Full Name Email Code
Priya Sharma priya@example.com EMP-FIN-001
Raj Patel raj@example.com EMP-TECH-002
Meera Singh meera@example.com EMP-MKT-003
ARJUN NAIR arjun@example.com emp-fin-004
Case Functions
=UPPER("priya sharma") → PRIYA SHARMA
=LOWER("ARJUN NAIR") → arjun nair
=PROPER("raj patel") → Raj Patel
PROPER capitalises the first letter of every word — perfect for cleaning names where someone typed in all caps or all lowercase.
=PROPER(A2) → converts "ARJUN NAIR" to "Arjun Nair"
→ converts "priya sharma" to "Priya Sharma"
TRIM — Remove Extra Spaces
TRIM removes all leading, trailing, and duplicate internal spaces (keeping single spaces between words):
=TRIM(" Meera Singh ") → "Meera Singh"
=TRIM(A4) → removes spaces from cell A4
When to use: After importing data from databases, CSV files, or copy-paste from the web — they often carry hidden spaces that break lookups and comparisons.
CLEAN — Remove Non-Printable Characters
=CLEAN(A2) → removes line breaks, tabs, and other invisible characters
Often combined with TRIM:
=TRIM(CLEAN(A2)) → clean + trim in one step
LEN — Count Characters
=LEN("Priya Sharma") → 12
=LEN(A2) → number of characters in A2
=LEN(TRIM(A2)) → length after trimming (helps detect extra spaces)
Useful for: Checking if a field exceeds a maximum length, or verifying fixed-length codes.
LEFT, RIGHT, MID — Extract Substrings
LEFT — Extract from the Left
=LEFT(text, num_chars)
=LEFT("Priya Sharma", 5) → "Priya"
=LEFT(A2, 3) → first 3 characters
=LEFT("EMP-FIN-001", 3) → "EMP"
RIGHT — Extract from the Right
=RIGHT(text, num_chars)
=RIGHT("EMP-FIN-001", 3) → "001"
=RIGHT(A2, FIND("@", A2)-1) → characters before @ (combined with FIND)
MID — Extract from the Middle
=MID(text, start_position, num_chars)
=MID("EMP-FIN-001", 5, 3) → "FIN" (start at 5, take 3 chars)
=MID(A2, 5, 3) → 3 characters starting at position 5
Combining LEFT, MID, RIGHT with LEN
-- Extract department from "EMP-FIN-001"
=MID(C2, 5, FIND("-", C2, 5) - 5)
→ Finds the second "-", extracts text between the two dashes
FIND and SEARCH — Locate Characters
=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])
| FIND | SEARCH | |
|---|---|---|
| Case sensitive? | Yes | No |
Wildcards (*, ?)? | No | Yes |
=FIND("@", "priya@example.com") → 6 (position of @)
=FIND("-", "EMP-FIN-001") → 4 (first hyphen)
=FIND("-", "EMP-FIN-001", 5) → 8 (second hyphen, start after 4)
=SEARCH("fin", "EMP-FIN-001") → 5 (case-insensitive)
Practical: Extract Email Domain
=MID(B2, FIND("@", B2)+1, LEN(B2))
→ "example.com" (everything after @)
Practical: Extract First Name
=LEFT(A2, FIND(" ", A2)-1)
→ "Priya" (everything before the first space)
Practical: Extract Last Name
=MID(A2, FIND(" ", A2)+1, LEN(A2))
→ "Sharma" (everything after the first space)
SUBSTITUTE — Replace Text
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=SUBSTITUTE("EMP-FIN-001", "-", "/") → "EMP/FIN/001"
=SUBSTITUTE("Hello World", "World", "Excel") → "Hello Excel"
=SUBSTITUTE(A2, " ", "_") → replace spaces with underscores
=SUBSTITUTE(A2, "-", "", 1) → remove only first hyphen
=SUBSTITUTE(A2, "-", "") → remove ALL hyphens
Instance number: Specify which occurrence to replace (default = all):
=SUBSTITUTE("1-2-3-4", "-", ":", 2) → "1-2:3-4" (only second hyphen)
REPLACE — Replace by Position
=REPLACE(old_text, start_num, num_chars, new_text)
=REPLACE("EMP-FIN-001", 1, 3, "STF") → "STF-FIN-001"
→ Replace the first 3 characters with "STF"
SUBSTITUTE replaces by matching text; REPLACE replaces by position.
CONCAT and TEXTJOIN
CONCAT (Excel 2019+) / CONCATENATE (older)
=CONCAT(A2, " | ", B2) → "Priya Sharma | priya@example.com"
=CONCATENATE(A2, " ", B2) → same result, older syntax
-- Combine with formatting:
="Employee: "&PROPER(A2)&" ["&C2&"]" → "Employee: Priya Sharma [EMP-FIN-001]"
TEXTJOIN (Excel 2019+)
Join a range with a delimiter, optionally skipping empty cells:
=TEXTJOIN(", ", TRUE, A2:A10)
→ "Priya Sharma, Raj Patel, Meera Singh, ..."
→ TRUE = ignore empty cells
=TEXTJOIN(" | ", FALSE, A2:A6)
→ joins all, includes empty cells as ""
TEXT — Format Numbers and Dates as Text
=TEXT(value, format_code)
=TEXT(75000, "₹#,##0") → "₹75,000"
=TEXT(TODAY(), "dd-mmm-yyyy") → "27-Jun-2026"
=TEXT(0.0856, "0.0%") → "8.6%"
=TEXT(44927, "dddd") → "Friday" (date → day name)
Use case: Build dynamic report titles:
="Report for "&TEXT(TODAY(), "MMMM YYYY") → "Report for June 2026"
VALUE — Convert Text to Number
When numbers are stored as text (from imports), convert them back:
=VALUE("75000") → 75000 (number)
=VALUE(A2) → converts text-number in A2 to actual number
Or use a double negative trick: =--A2 (unary minus twice converts text to number).
Practical Examples
Example 1: Clean an Employee Import
Raw data imported from HR system:
A2: " PRIYA SHARMA " (extra spaces, all caps)
B2: "9988776655" (phone, should be formatted)
=PROPER(TRIM(A2)) → "Priya Sharma"
=TEXT(VALUE(B2), "00000 00000") → "99887 76655"
Example 2: Parse Product Codes
EMP-FIN-001-2026 → extract each segment:
=LEFT(A2, FIND("-",A2)-1) → "EMP" (type)
=MID(A2, 5, FIND("-",A2,5)-5) → "FIN" (dept)
=MID(A2, 9, FIND("-",A2,9)-9) → "001" (number)
=RIGHT(A2, 4) → "2026" (year)
Example 3: Generate Usernames
From full name → firstname.lastname@company.com:
First name: =LOWER(LEFT(A2, FIND(" ",A2)-1))
Last name: =LOWER(MID(A2, FIND(" ",A2)+1, LEN(A2)))
Email: =LOWER(LEFT(A2,FIND(" ",A2)-1))&"."&LOWER(MID(A2,FIND(" ",A2)+1,LEN(A2)))&"@meritshot.com"
→ priya.sharma@meritshot.com
Example 4: Format a Phone Number
A2: 9876543210 (raw number)
=TEXT(A2, "00000 00000") → "98765 43210"
=LEFT(TEXT(A2,"0000000000"),5)&"-"&RIGHT(TEXT(A2,"0000000000"),5) → "98765-43210"
Example 5: Count Words in a Cell
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2)," ","")) + 1
→ counts words by counting spaces + 1
REPT — Repeat Text
=REPT("★", 5) → "★★★★★"
=REPT("█", A2/1000) → text-based bar chart! (if A2 = 5000 → █████)
Common Mistakes
1. FIND returns #VALUE! when text not found
=FIND("@", "no at sign here") → #VALUE! error
Protect with IFERROR:
=IFERROR(FIND("@", A2), 0) → returns 0 if not found
2. TRIM doesn't remove non-breaking spaces
=TRIM(A2) → still has spaces if they're (from web copy-paste)
Fix: =SUBSTITUTE(TRIM(A2), CHAR(160), "") → CHAR(160) is non-breaking space
3. LEN counts ALL characters including spaces
=LEN(" hello ") → 9 (includes the spaces)
=LEN(TRIM(" hello ")) → 5 (trim first)
4. Text comparison is case-insensitive by default
=IF(A2="Finance", ...) → matches "finance", "FINANCE", "Finance"
For case-sensitive: =IF(EXACT(A2, "Finance"), ...)
Practice Exercises
- Extract first and last names from a full name in column A using LEFT, FIND, and MID.
- Clean a column of employee names that have leading/trailing spaces and mixed case.
- From email addresses like
priya@meritshot.com, extract just the username (priya) and the domain (meritshot.com). - Use TEXTJOIN to combine a list of 5 city names into a single comma-separated string, skipping blanks.
- Build a formula that generates a display label:
"[EMP001] Priya Sharma – Finance"from separate columns for code, name, and department.
Summary
In this chapter you learned:
UPPER(),LOWER(),PROPER()— case conversionTRIM()— remove leading, trailing, and duplicate spacesCLEAN()— remove non-printable charactersLEN()— count charactersLEFT(text, n),RIGHT(text, n),MID(text, start, n)— extract substringsFIND(search, text)(case-sensitive) vsSEARCH()(case-insensitive, wildcards)SUBSTITUTE(text, old, new)— replace by matching;REPLACE(text, pos, n, new)— replace by positionCONCAT()/TEXTJOIN(delim, skip_blanks, range)— combine textTEXT(value, format_code)— format numbers/dates as display textVALUE()— convert text-number back to a number- Combine functions:
PROPER(TRIM(A2)),LEFT(A2, FIND(" ",A2)-1)
Next up: Lookup Functions — VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP.