SheetsdrillLEFT, RIGHT and MID
LEFT(string, [number_of_characters]) returns the first characters of a text, RIGHT the last, and MID(string, starting_at, extract_length) a piece from the middle starting at a position. Combine them with FIND or SEARCH when the position depends on a separator.
Syntax
=LEFT(string, [number_of_characters]), =RIGHT(string, [number_of_characters]), =MID(string, starting_at, extract_length)| Part | What it is |
|---|---|
string | The cell or text to extract from. |
number_of_characters | How many characters to return, default 1. |
starting_at | MID only: the position of the first character to return, counting from 1. |
extract_length | MID only: how many characters to return. |
Examples
=LEFT("INV-2041-US", 3)Result: INV
=RIGHT("INV-2041-US", 2)Result: US
=MID("INV-2041-US", 5, 4)Result: 2041
Common mistakes
- The result of LEFT or MID is text, so 2041 does not sum; wrap in VALUE.
- #VALUE! from FIND when the separator is missing; wrap in IFERROR or use SEARCH with a fallback.
- A fixed count breaks on variable-length data (Ana versus Alexandra); compute the position with FIND, or use SPLIT or REGEXEXTRACT.
Practice LEFT, RIGHT and MID
These drills check your answer on live data, free, no account needed.
- Pull out the invoice numberExtract the invoice number from each payment note as a real number, so it can be sorted and matched. Fill B2 and B3 using REGEXEXTRACT.
Questions
How do I get text after a certain character?
=MID(A2, FIND("@", A2)+1, LEN(A2)) returns everything after the @ sign; or =REGEXEXTRACT(A2, "@(.*)").
How do I get the last word?
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100)) or simpler in Sheets: =REGEXEXTRACT(A2, "\S+$").
Related
- SPLITHow SPLIT in Google Sheets breaks text into separate cells by a comma, space or other delimiter, how to keep only the first or last piece, and common errors.
- LENHow LEN in Google Sheets counts the characters in a cell, how to find values that are too long or short, count words, and why hidden spaces change the number.
- REGEXMATCH, REGEXEXTRACT and REGEXREPLACEHow the regex functions in Google Sheets work: test a pattern with REGEXMATCH, pull text out with REGEXEXTRACT and clean it with REGEXREPLACE, with examples.
- SUBSTITUTEHow SUBSTITUTE in Google Sheets replaces one piece of text with another, removes characters, targets one occurrence, and how it differs from REGEXREPLACE.