How to Use LEFT/RIGHT/MID in Google Sheets
=LEFT(string, [n]), =RIGHT(string, [n]) and =MID(string, starting_at, extract_length).
What these functions do
Codes, IDs and reference numbers often have a fixed layout. LEFT, RIGHT and MID cut a piece out of text by position, so you can pull out a prefix, a year or a suffix without retyping it.
Syntax
=LEFT(string, [number_of_characters])
=RIGHT(string, [number_of_characters])
=MID(string, starting_at, extract_length)
string: the text to cut from, usually a cell.number_of_characters(optional): how many characters to return. It defaults to 1, and 0 returns an empty string.starting_at: the position where MID begins. The first character is position 1.extract_length: how many characters MID returns.
A step-by-step example
Cell A2 holds the invoice code INV-2026-0042.
| A | |
|---|---|
| 1 | Code |
| 2 | INV-2026-0042 |
The first three characters:
=LEFT(A2, 3)
This returns INV. For the last four characters, =RIGHT(A2, 4) returns 0042.
To take the year from the middle, count the positions: I is 1, N is 2, V is 3, the hyphen is 4, and the year starts at position 5.
=MID(A2, 5, 4)
This returns 2026: four characters starting at position 5.
Taking the rest of a string
Google's MID page says that if the text ends before the requested length, MID returns everything up to the end, and that you can use LEN to work out the remaining length. To find a starting point that is not fixed, use SEARCH to locate a character and pass that position to MID.
Common mistakes
- Counting from 0. Positions start at 1.
- Forgetting the hyphens and spaces. They count as characters.
- Results that are text. The year
2026returned by MID is text. Wrap it in VALUE if you need a number. - Varying lengths. If the parts of your codes change length, SPLIT with a delimiter is usually the better tool.
Try it yourself
Use the code above (A2 holds INV-2026-0042). Type your formula into the box, starting with =, then press Check.
Exercise 1: The prefix
Write a formula that returns the first three characters of the code in A2.
Exercise 2: The year
Write a formula that returns the four-digit year from the middle of the code in A2 (it starts at position 5).
What to learn next
- SPLIT: break text into separate cells.
- SUBSTITUTE: replace text inside a string.
- TEXTJOIN: combine text from many cells with a separator.
- REGEXEXTRACT: pull text out with a pattern.
- LEN: count the characters in a cell.
Syntax and rules in this guide were checked against Google's LEFT/RIGHT/MID help page. See all guides.