Text Functions
Text functions clean and cut text. Examples use the word JKEdusphere (11 characters).
| Function | Syntax | Example | Result |
|---|---|---|---|
| LEN | =LEN(text) | =LEN("JKEdusphere") | 11 (spaces count) |
| LEFT | =LEFT(text, [n]) | =LEFT("JKEdusphere",2) | JK |
| RIGHT | =RIGHT(text, [n]) | =RIGHT("JKEdusphere",4) | here |
| MID | =MID(text, start, n) | =MID("JKEdusphere",3,3) | Edu |
| FIND | =FIND(find_text, within_text, [start]) | =FIND("s","JKEdusphere") | 6. Case-sensitive, no wildcards. |
| SEARCH | =SEARCH(find_text, within_text, [start]) | =SEARCH("S","JKEdusphere") | 6. Not case-sensitive, allows wildcards. |
| UPPER / LOWER | =UPPER("jammu") | JAMMU / jammu | |
| PROPER | =PROPER("jammu and kashmir") | Jammu And Kashmir | |
| TRIM | =TRIM(" Srinagar city ") | Srinagar city (removes extra spaces, keeps one between words) | |
| CLEAN | =CLEAN(text) | Removes non-printing characters | |
| SUBSTITUTE | =SUBSTITUTE(text, old, new, [instance]) | =SUBSTITUTE("2026-10-21","-","/") | 2026/10/21. Replaces by content. |
| REPLACE | =REPLACE(old_text, start, n, new_text) | =REPLACE("JK2025",3,4,"2026") | JK2026. Replaces by position. |
| CONCATENATE / CONCAT | =CONCAT(text1, text2, ...) | =A2&" - "&B2 | Aamir - Srinagar |
| TEXTJOIN (2019+) | =TEXTJOIN(delimiter, ignore_empty, text1, ...) | =TEXTJOIN(", ",TRUE,A2:A4) | Aamir, Bilal, Charu |
| EXACT | =EXACT(text1, text2) | =EXACT("Jammu","jammu") | FALSE (case-sensitive compare) |
| REPT | =REPT(text, n) | =REPT("*",5) | ***** |
| VALUE | =VALUE("123")+1 | 124 (text to number) | |
| TEXT | =TEXT(value, format) | =TEXT(1234.5,"#,##0.00") | 1,234.50 |
=TEXT(DATE(2026,10,21),"dd-mmm-yyyy") | 21-Oct-2026 | ||
=TEXT(DATE(2026,10,21),"dddd") | Wednesday | ||
=TEXT(0.256,"0.0%") | 25.6% | ||
| CHAR / CODE | =CHAR(65) / =CODE("A") | A / 65 | |
| TEXTBEFORE / TEXTAFTER / TEXTSPLIT (365) | =TEXTBEFORE("Ali Khan"," ") | Ali |
Handy combinations
| Need | Formula |
|---|---|
| First name from "Ali Khan" in A1 | =LEFT(A1,FIND(" ",A1)-1) |
| Last name | =RIGHT(A1,LEN(A1)-FIND(" ",A1)) |
| Count the words in A1 | =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1 |
| Count how often "a" occurs | =LEN(A1)-LEN(SUBSTITUTE(A1,"a","")) |
| Remove all spaces | =SUBSTITUTE(A1," ","") |
| Make a code like JK-001 | ="JK-"&TEXT(ROW()-1,"000") |
| Show a number with leading zeros | =TEXT(7,"000") → 007 |
Without formulas you can use Flash Fill (Ctrl + E), which copies a pattern you type once, and Text to Columns (Data tab), which splits text at a space, comma or fixed width.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.