Excel text functions
Text functions pull pieces out of strings, clean up imported data and join values into labels. Most real cleanup is a short chain of them: TRIM the spaces, fix the case with PROPER, then split with TEXTBEFORE, or with LEFT and FIND in older versions.
- Functions
- 24
- With a graded exercise
- All 24
- Start with
TRIM
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Remove extra spaces from imported data | TRIM | Strips leading, trailing and repeated spaces in one step, the first fix when lookups fail. |
| Split text at a character, like the @ in an email | TEXTBEFORE | No position math, and TEXTAFTER returns the other half. In older versions, use LEFT with FIND. |
| Take a fixed number of characters from the start | LEFT | Best for codes with a fixed-length prefix. RIGHT and MID handle the end and the middle. |
| Join a list into one cell with commas | TEXTJOIN | Puts the delimiter between items and can skip blanks. |
| Show a number or date inside a sentence | TEXT | Keeps formatting like $1,250.00 or Mar 7 when you join values with &. |
| Check whether a cell contains a word | SEARCH | Wrap it in ISNUMBER for a TRUE or FALSE answer that ignores case. |
Try TRIM live
The most useful text function, on a sample sheet you can edit. Change a value and watch the results update.
D2=TRIM(A2) Maya Chen Leading spaces removedD3=LEN(A3)-LEN(TRIM(A3)) 2 How many extra spaces the cell hadD4=TRIM(C4) Ships Monday Runs of spaces between words become one
All 24 text functions
| Function | What it does | Practice exercise |
|---|---|---|
CHAR function | CHAR returns the character for a code number. | Two-line labels |
CLEAN function | CLEAN removes non-printing characters, such as line breaks and tabs, from text. | Strip hidden characters |
CODE function | CODE returns the numeric code of the first character in a text string. | Spot hidden characters |
CONCAT functionExcel 2019 and later | CONCAT joins text from several cells or whole ranges into one string. | Build SKUs |
CONCATENATE function | CONCATENATE joins up to 255 text items into one string. | Build email addresses |
EXACT function | EXACT compares two text values and returns TRUE only if they match exactly, including upper and lower case. | Case-sensitive code check |
FIND function | FIND returns the position of one piece of text inside another, counting from 1. | The classic way |
LEFT function | LEFT returns a set number of characters from the start of a text string. | City code |
LEN function | LEN counts the characters in a text string, including spaces and punctuation. | Check ID lengths |
LOWER function | LOWER converts every letter in a text string to lowercase and leaves numbers and symbols alone. | Clean emails |
MID function | MID returns characters from the middle of a text string, starting at a position you choose. | Year from the middle |
PROPER function | PROPER capitalizes the first letter of each word and lowercases the rest. | Clean names |
REPLACE function | REPLACE swaps out a set number of characters at a given position, whatever those characters are. | Mask account numbers |
REPT function | REPT repeats a piece of text a given number of times. | Bars in cells |
RIGHT function | RIGHT returns a set number of characters from the end of a text string. | Sequence number |
SEARCH function | SEARCH returns the position of one piece of text inside another, ignoring upper and lower case, and it understands the ? and * wildcards. | Flag reviews about delivery |
SUBSTITUTE function | SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose. | Digits only |
TEXT function | TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026". | Month labels |
TEXTAFTER functionExcel 2024 and Microsoft 365 | TEXTAFTER returns everything after a delimiter, such as the domain after the @ or the last part of a file path. | Domain |
TEXTBEFORE functionExcel 2024 and Microsoft 365 | TEXTBEFORE returns everything before a delimiter you name, such as the username before the @ in an email or the first name before a space. | Username |
TEXTJOIN functionExcel 2019 and later | TEXTJOIN joins a list of values with the same delimiter between each one, and can skip blanks. | One-cell list |
TRIM function | TRIM removes spaces from the start and end of text and shrinks runs of spaces between words to one. | Clean names |
UPPER function | UPPER converts every letter in a text string to capitals. | Standardize SKUs |
VALUE function | VALUE converts text that looks like a number into a real number Excel can add, average and compare. | Fix amounts stored as text |
Head-to-head
- FIND vs SEARCHBoth return the position of text inside other text. FIND is case-sensitive and takes the text literally; SEARCH ignores case.
- CONCAT vs TEXTJOIN vs &All three join text. The & operator is fine for two or three pieces.
Learn them in order
These functions are taught step by step in the Text cleanup track. Each lesson ends with graded exercises on real-looking data.
- LEFT, RIGHT, MID & LEN8 min · Pro
- Joining text7 min · Pro
- Cleaning messy data8 min · Pro
- TEXTBEFORE, TEXTAFTER & FIND8 min · Pro
- DATE, YEAR, MONTH & TEXT8 min · Pro