Arguments
text- The text value to extract from.num_chars- How many characters to return.start_num(optional) - For MID, the position of the first character to return.
LEFT, RIGHT & MID
Extract a fixed number of characters from the start, end, or middle of a text value.
Syntax
=LEFT(text, num_chars) =RIGHT(text, num_chars) =MID(text, start_num, num_chars)=LEFT(A2, 3)Returns: First 3 characters of A2
Useful for codes and prefixes.
=MID(A2, 4, 2)Returns: 2 characters starting at position 4
Pull a slice out of the middle.
How LEFT, RIGHT & MID evaluates
LEFT, RIGHT and MID cut a fixed number of characters out of a text value. LEFT counts from the start, RIGHT from the end, and MID from a position you give it. All three are 1-based: the first character is position 1, not 0, which is the detail most often responsible for a result that is off by one. LEFT and RIGHT treat num_chars as optional and default it to 1; MID requires all three arguments.
Fixed positions only work on fixed-width data. The moment a code, a name or an address varies in length, these functions have to be paired with FIND, SEARCH or LEN to compute the position. =LEFT(A2, FIND(" ", A2)-1) takes everything before the first space; =RIGHT(A2, LEN(A2)-FIND("@", A2)) takes the domain from an email address. FIND is case-sensitive and takes no wildcards; SEARCH ignores case and accepts * and ?. Either returns #VALUE! when the character is absent, which is why these formulas are usually wrapped in IFERROR.
Two behaviours matter for the output. Asking for more characters than the text holds is not an error - LEFT("AB", 10) returns "AB" - but asking for a negative count is #VALUE!. And the result is always text, even when every character is a digit: =LEFT(A2, 4) on a year code returns "2024" as a string, which will not sum and will not match a numeric 2024 in a lookup. Wrap it in VALUE when a number is what you need. In Excel 2021 and 365, TEXTBEFORE, TEXTAFTER and TEXTSPLIT usually express the same intent far more directly.
- Positions are 1-based
- MID(A2, 1, 3) is the same as LEFT(A2, 3). A start_num of 0 or less returns #VALUE!; a start_num past the end of the text returns an empty string, not an error.
- num_chars beyond the length
- Returns everything available, without complaint. =RIGHT("AB", 99) is "AB". A num_chars of 0 returns an empty string; a negative one returns #VALUE!.
- The result is text
- Even when it is all digits. =LEFT(A2, 4)+0 or =VALUE(LEFT(A2, 4)) converts it back to a number for arithmetic or for matching against a numeric column.
- Numbers and dates as input
- The stored value is used, not the display. LEFT on a cell showing 15/03/2024 works on the serial number 45366 and returns "45". Convert first with TEXT(A2, "dd/mm/yyyy") if you want to cut up the visible date.
- Spaces count as characters
- A leading space shifts everything by one, which is why TRIM belongs in front of these functions on imported data: =LEFT(TRIM(A2), 3).
- Byte variants
- LEFTB, RIGHTB and MIDB count bytes rather than characters and only differ from the plain versions when a double-byte character set language is enabled. In every other setup they behave identically.
Errors LEFT, RIGHT & MID returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | num_chars is negative, or MID's start_num is below 1. Most often the cause is an arithmetic expression that went negative, as in LEN(A2)-FIND(...) when FIND failed on a row without the delimiter. |
| #VALUE! from a nested FIND or SEARCH | The character being searched for is not in that row's text, so FIND returns #VALUE! and the surrounding LEFT or MID passes it on. Wrap the whole formula in IFERROR, or test with ISNUMBER(FIND(...)) first. |
| #NAME? | A typo in the function name, or TEXTBEFORE and TEXTAFTER used in a version older than Excel 2021 - both are easy to reach for when a LEFT or MID formula gets long. |
| #REF! | The referenced cell was deleted. The formula text still looks right, so check the reference rather than the character counts. |
More examples
=LEFT(A2, FIND(" ", A2 & " ")-1)Returns: The first word
Appending a space to the search text means a single-word entry still finds a delimiter, so no IFERROR is needed for rows without a space.
=MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2) - 1)Returns: The middle segment of a three-part code such as UK-LON-042
The second FIND starts searching just past the first hyphen. In Excel 2021 and 365 this is =TEXTBEFORE(TEXTAFTER(A2, "-"), "-").
=VALUE(RIGHT(A2, 4))Returns: The last four digits as a number
RIGHT returns text; VALUE makes it numeric so it can be compared or summed. It returns #VALUE! if any of the last four characters is not a digit.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | All three supported, unchanged since the earliest versions, along with LEFTB, RIGHTB and MIDB. |
| Excel 2019 | Identical behaviour. |
| Excel 2021 / Microsoft 365 | Identical, but TEXTBEFORE, TEXTAFTER and TEXTSPLIT usually replace the FIND-and-LEN arithmetic entirely. |
| Google Sheets | Same syntax and same 1-based positions. Sheets adds SPLIT and REGEXEXTRACT, which cover most cases where MID is being used to parse. |
| LibreOffice Calc | All three supported with identical arguments. Calc's regular-expression support in SEARCH is broader than Excel's wildcards, which changes what a nested SEARCH matches. |
Does LEFT, RIGHT & MID Work in Google Sheets?
Yes. LEFT, RIGHT & MID uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements LEFT, RIGHT and MID. LEFT and RIGHT take one or two arguments and default the character count to 1 when it is omitted; MID requires all three, matching Excel.
A negative character count returns #VALUE! from all three, and a MID start position below 1 does the same. A count larger than the text simply returns the whole remaining string, and a fractional count is truncated toward zero.
The first argument is converted to text from its stored value, so LEFT applied to a date cell operates on the date serial number rather than on the formatted date, exactly as Excel does.
The byte-counting variants LEFTB, RIGHTB and MIDB are not implemented in the editor and return #NAME?; LEN, FIND, SEARCH, TRIM, VALUE, TEXTBEFORE, TEXTAFTER and TEXTSPLIT are all available to build the same results.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
LEFT, RIGHT & MID questions
Because dates are numbers wearing a format. A2 holds a serial such as 45366 and LEFT works on the value, returning the first two digits of that number. To slice the date as you see it, convert it to text first: =LEFT(TEXT(A2, "dd/mm/yyyy"), 2). The same happens with currency and percentages.
Reverse the problem: replace the final space with a marker character no one uses, then take everything after it. =TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100)) is the classic version - it pads every space to 100 characters so the last 100 always start inside the final word. In Excel 2021 and 365, =TEXTAFTER(A2, " ", -1) does it in one step.
Almost always the 1-based start. MID(A2, 4, 2) begins at the fourth character, so to skip a three-character prefix the start is 4, not 3. When the position comes from FIND, remember FIND returns the position of the delimiter itself: you want FIND("-", A2)+1 to start after it, and a -1 on the length to leave it out.
Text to Columns is a one-off transformation that produces static values and is faster for a single cleanup. LEFT, RIGHT and MID are formulas that keep updating as the source changes, which is what you want in a template that receives a new export every week. The formulas also survive the source column being refreshed in place, where Text to Columns has to be run again.