📎 This article includes 1 downloadable practice file ↓
In this article
Customer lists from forms and exports arrive as ” ravi KUMAR ” and “+91-98765 43210”. These five functions fix the usual problems.
| Function | Refers to | Turns |
|---|---|---|
| CLEANNAME | =LAMBDA(t,PROPER(TRIM(t))) |
” ravi KUMAR ” → Ravi Kumar |
| INITIALS | =LAMBDA(t,CONCAT(LEFT(TEXTSPLIT(TRIM(t)," "),1))) |
Ravi Kumar → RK |
| DIGITSONLY | =LAMBDA(t,CONCAT(IFERROR(--MID(t,SEQUENCE(LEN(t)),1),""))) |
+91-98765 43210 → 919876543210 |
| WORDCOUNT | =LAMBDA(t,ROWS(TEXTSPLIT(TRIM(t),," "))) |
“Excel is fun to learn” → 5 |
| MASK | =LAMBDA(t,REPT("*",LEN(t)-4)&RIGHT(t,4)) |
9876543210 → ******3210 |
How DIGITSONLY works
SEQUENCE(LEN(t)) makes 1, 2, 3 … one number per character. MID pulls each character out, -- tries to turn it into a number (letters and symbols become errors), IFERROR blanks the errors, and CONCAT joins what’s left. That is the pattern for any “keep only these characters” function.
=LAMBDA(t,LET(d,DIGITSONLY(t),RIGHT(d,10))) strips the 91 country code.Common mistakes
- PROPER on brand names like “iPhone” or “McDonald’s” breaks them. Keep an exceptions list for those.
- MASK on short text: if LEN is under 4, REPT gets a negative count. Guard with
MAX(0,LEN(t)-4).
Practice
Download the workbook below. The tasks call each function inline, like =LAMBDA(x,x*2)(A2), so the file works on any Microsoft 365 PC; in your own files, save them by name in Name Manager. The Check column turns green when you’re right.
📎 Practice files for this article
- 📗Lesson 3 practice workbook6 text helpers to build and check.⬇ 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.