📎 This article includes 1 downloadable practice file ↓
In this article
Data exported from software or typed by many people is messy: “ ravi KUMAR ”, mobile numbers with spaces and dashes, codes like INV-2026-0145 that hold the year inside. Text functions clean and split all of this with formulas that work on 10 rows or 10,000.
Cleaning
| Function | Does | “ ravi KUMAR ” becomes |
|---|---|---|
| TRIM | Removes leading/trailing spaces and squeezes inner spaces to one | “ravi KUMAR” |
| PROPER | Capital first letter of each word | “Ravi Kumar” (with TRIM) |
| UPPER / LOWER | All capitals / all small | “RAVI KUMAR” / “ravi kumar” |
| CLEAN | Removes non-printing characters from exports |
=PROPER(TRIM(A2))Functions nest: TRIM runs first, PROPER fixes its result.
Measuring and cutting
=LEN(B2) ' number of characters: "INV-2026-0145" → 13
=LEFT(B2, 3) ' first 3 → "INV"
=RIGHT(B2, 4) ' last 4 → "0145"
=MID(B2, 5, 4) ' 4 characters starting at position 5 → "2026"
=RIGHT(C2, 6) ' PIN code at the end of an addressFinding a position
When pieces vary in length, find the separator first:
=FIND(" ", A3) ' position of the first space
=LEFT(A3, FIND(" ", A3) - 1) ' first name
=MID(A3, FIND(" ", A3) + 1, 50) ' everything after the space: last name
=FIND(",", C3) ' first comma in an addressFIND is case-sensitive; SEARCH isn’t, and SEARCH allows wildcards. Both return #VALUE! if the text isn’t there; wrap in IFERROR if some rows may not have it.
Replacing
=SUBSTITUTE(D2, " ", "") ' "98765 43210" → "9876543210"
=SUBSTITUTE(D3, "-", "") ' remove dashes
=SUBSTITUTE(A2, "Pvt. Ltd.", "Pvt Ltd")SUBSTITUTE swaps every occurrence of some text. (REPLACE swaps characters by position, which is less often useful.)
Joining
=A2 & " " & B2 ' join with a space
=LEFT(A3, FIND(" ", A3)-1) & "-" & B3 ' "anita-INV-2026-0072"
=LEFT(A4,1) & "." & MID(A4, FIND(" ",A4)+1, 1) & "." ' initials "M.I."
=TEXTJOIN(", ", TRUE, A2:A5) ' Excel 2019+: join a range with commasFlash Fill or formulas?
Flash Fill (Ctrl+E, Lesson 2) is quicker for a one-off. Formulas update when the data changes, and they show exactly what they did, so prefer them for anything you’ll repeat monthly. Excel 365 also has TEXTBEFORE, TEXTAFTER and TEXTSPLIT, which make splitting even easier: see splitting text by delimiter.
Where beginners go wrong
| Problem | Cause |
|---|---|
| Lookups fail on names that look identical | Hidden spaces; TRIM both sides |
| #VALUE! from FIND | Separator not in that row |
| First name includes the space | Forgot the -1 in LEFT(A2, FIND(” “,A2)-1) |
| Results won’t add up | They’re text; convert with VALUE |
| Formula breaks when the source column is deleted | Paste the results as values once done |
Practice
Download this lesson’s workbook below. The Text sheet has messy names, invoice codes, addresses and mobile numbers. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 12 practice workbookMessy names, invoice codes, addresses and mobile numbers: 12 cleaning and splitting tasks.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.