LEFT, RIGHT and MID Functions
Excel has no SUBSTRING function: LEFT(A2,5) returns the first 5 characters, RIGHT(A2,4) the last 4, and MID(A2,7,3) three characters from position 7. MID counts from 1 rather than 0, which is by far the most common reason a result comes back one character out. To trim from the opposite end you pair them with LEN: =LEFT(A2,LEN(A2)-4) drops the last four characters and =MID(A2,4,LEN(A2)) drops the first three. When the part you want sits between two markers instead of at a fixed position, wrap FIND around the positions — or, on Excel 365, skip the arithmetic altogether and use TEXTBEFORE and TEXTAFTER.
The formula
=LEFT(A2, 5) the first 5 characters
=RIGHT(A2, 4) the last 4 characters
=MID(A2, 7, 3) 3 characters starting at position 7 (positions count from 1)
=LEFT(A2, LEN(A2)-4) everything except the last 4 characters
=MID(A2, 4, LEN(A2)) everything except the first 3 characters
=MID(A2, FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1) the text between the first ( and the first )A worked example
A2 holds the order reference "INV-2026-00871 (Ontario)".
=LEFT(A2,3) | =MID(A2,5,4) | =RIGHT(A2,9) | =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)
"INV", "2026", "(Ontario)" and "Ontario". The fourth is the pattern worth memorising: FIND locates each marker, the subtraction gives the distance between them, and the -1 removes the closing bracket itself. On Excel 365, =TEXTAFTER(TEXTBEFORE(A2,")"),"(") returns the same "Ontario" with no position arithmetic at all.
Which one do I need?
| If you want to… | Use |
|---|---|
| You need a fixed number of characters from the start | =LEFT(text, num_chars) |
| You need a fixed number of characters from the end | =RIGHT(text, num_chars) |
| You need characters from somewhere in the middle | =MID(text, start_num, num_chars) — start_num counts the first character as 1, not 0 |
| You want to strip a fixed number of characters off either end | =LEFT(A2,LEN(A2)-n) to drop the last n, =MID(A2,n+1,LEN(A2)) to drop the first n |
| The piece sits between two markers, like brackets or a dash | MID with FIND on both markers in older Excel; TEXTBEFORE and TEXTAFTER on Excel 365 |
| The parts vary in length but a delimiter separates them consistently | Splitting is the better tool than counting characters — TEXTSPLIT or Text to Columns |
| You want to count characters rather than extract them | =LEN(A2), the function these formulas lean on for anything measured from the right-hand end |
| MID returns #VALUE! or an empty string | start_num below 1 gives #VALUE!; a start_num past the end of the text returns empty. Check what FIND handed it — FIND returns #VALUE! when the marker is absent |
Frequently asked questions
Is there a SUBSTRING function in Excel?
No function of that name exists. LEFT, RIGHT and MID are the substring functions — LEFT and RIGHT take characters from either end, and MID takes them from a position you specify. Excel 365 adds TEXTBEFORE and TEXTAFTER, which cut at a marker rather than at a counted position.
Why is my MID formula off by one character?
MID counts the first character of the text as position 1, not position 0. If you are converting logic from a programming language that uses zero-based indexes, every start_num needs one added to it.
How do I remove the first 3 characters from a cell?
Use =MID(A2,4,LEN(A2)), which starts at the fourth character and takes everything remaining, or =RIGHT(A2,LEN(A2)-3) for the same result. LEN keeps it working whatever the length of each individual cell.
How do I extract text between two characters?
Use =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1), where FIND locates each marker and the subtraction gives the length between them. On Excel 365 the same result comes from =TEXTAFTER(TEXTBEFORE(A2,")"),"(").
New guides and tools, once a month
DE + EN · double opt-in · no spam