Text functions
LEFT, RIGHT, MID, LEN, TRIM, SEARCH, and SUBSTITUTE: the everyday toolkit for slicing, inspecting, and cleaning up text.
These seven functions cover almost everything day-to-day spreadsheet work needs from a text value: grabbing the first few characters of a code, pulling a middle segment out of a longer string, measuring length, cleaning up stray spaces from a pasted import, finding where one piece of text sits inside another, and swapping one substring for another.
All seven count character positions starting at 1, not 0, and all seven treat a typed literal exactly the same as text pulled from a cell. SUBSTITUTE is the one most often reached for on messy imported data: a price exported as the text "$0.75" instead of a real number becomes usable with one formula that strips the stray character and converts it back to a number in the same step.
Syntax
| Argument | Description |
|---|---|
| LEFT / RIGHT | Return a fixed number of characters from the start or end of a text value. |
| MID | Returns a substring starting at a given position, for a given length. |
| LEN | Returns how many characters are in a text value. No other arguments. |
| TRIM | Collapses extra spaces down to single spaces and trims the ends. |
| SEARCH | Returns the position where one piece of text is found inside another (case-insensitive); no match returns an error. |
| SUBSTITUTE | Replaces every occurrence of old_text with new_text inside text. Add instance_num to replace only the Nth occurrence instead of all of them. |
A real example: turning an imported price string back into a number
A data export that stored a dollar amount as text, dollar sign included:
SUBSTITUTE strips the "$" character, leaving behind the text "0.75"; multiplying by 1 is a quick, common way to force that cleaned-up text back into a real number Excel can add up and compare numerically, rather than a VALUE() call, though either works.
Worked examples
| A | |
|---|---|
| 1 | Code |
| 2 | US-4471 |
| A | |
|---|---|
| 1 | Code |
| 2 | US-4471-A |
| A | |
|---|---|
| 1 | Description |
| 2 | Laptop Pro 14-inch |
| A | |
|---|---|
| 1 | Fee (raw) |
| 2 | $0.75 |
(the *1 turns the cleaned-up text "0.75" into a real number)
Common mistakes
- Off-by-one errors in MID's start position. Count characters from 1, including any punctuation.
- Using RIGHT/LEFT with a fixed length on text that isn't actually a fixed width. Codes that vary in length break this fast.
- Forgetting SEARCH returns a position, not the found text itself. That trips people expecting VLOOKUP-like behavior.
- Using SUBSTITUTE to clean up a value and then forgetting it's still text afterward. A formula further downstream that adds or multiplies it needs that *1 (or a wrapping VALUE) first.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice text functions →