In Excel, RIGHT extracts characters from the end of text, LEFT extracts them from the beginning, and MID extracts characters from a position you specify. Use them to pull codes, initials, dates, or other parts out of a cell without changing the original value.
What RIGHT, LEFT, and MID Do
These three text functions return part of a text value. The main difference is where each one starts: LEFT begins at the first character, RIGHT begins at the last, and MID begins at a numbered position within the text. They are available in Microsoft Excel for Microsoft 365 on Windows and in many earlier Excel versions.
For a consistent example, suppose cell A2 contains INV-2025-047. Counting from the left, the characters are I at position 1, N at 2, V at 3, a hyphen at 4, and 2 at 5. Counting from the right, 7 is the first character, 4 is the second, and 0 is the third. MID uses positions counted from the left, not from the right.
- LEFT is useful when the part you need always begins at the start, such as a three-letter department code.
- RIGHT is useful when the part you need always ends the text, such as a final set of digits.
- MID is useful when the part you need is between other characters or starts at a known position.
These functions extract characters, not whole words or numeric values as a special operation. A result that looks like a number may still be text, which matters if you later need to calculate with it.
Function Syntax and Arguments
Each function takes a text value or cell reference. RIGHT and LEFT have an optional character-count argument; if you leave it out, Excel returns one character. MID requires both a starting position and a character count.
=LEFT(text, num_chars)returns the specified number of characters from the start of the text.=RIGHT(text, num_chars)returns the specified number of characters from the end of the text.=MID(text, start_num, num_chars)returns the specified number of characters beginning at the position you give.
In these templates, text can be a cell reference such as A2 or text in quotation marks. num_chars is the number of characters to return. In MID, start_num is the position of the first character to return, with the first character counted as 1. Excel formulas use commas between arguments in the standard US-style syntax shown here. Some regional settings display semicolons instead; if Excel rejects a formula with commas, your list-separator setting may be the reason.
Basic Examples With a Cell Reference
Assume A2 contains INV-2025-047. The following examples show what each function returns. Each formula points to the same source cell, so you can put the formulas in other cells without overwriting the original text.
With the text INV-2025-047 in A2, the formula =LEFT(A2,3) returns INV. It takes the first three characters, so the hyphen and the rest of the identifier are excluded.
With the same value in A2, the formula =RIGHT(A2,3) returns 047. This is a text result: Excel preserves the leading zero in the extracted characters. If you convert that result to a number, the leading zero may disappear.
With the same value in A2, the formula =MID(A2,5,4) returns 2025. The fifth character is 2, and the next four characters are 2, 0, 2, and 5. The starting position is counted across the entire text, including punctuation.
These examples use fixed character counts. That works well when entries follow a consistent pattern. If identifiers vary in length or their separators move, a formula based on a separator or another rule may be more dependable.
Extracting Text Around a Separator
A common task is to split a code at a hyphen, slash, space, or other separator. If the part you want always occupies the same positions, LEFT, RIGHT, or MID with a fixed count is straightforward. When the position can vary, combine a text function with FIND, which returns the position of a character or text string.
With INV-2025-047 in A2 and the year always four characters after the first hyphen, the formula =MID(A2,FIND("-",A2)+1,4) returns 2025. FIND locates the first hyphen; adding 1 moves the starting position to the character immediately after it. MID then takes four characters.
This approach is more adaptable than hard-coding a starting position if the prefix can have different lengths. However, it still assumes that the first hyphen is the relevant separator and that the desired part has four characters. If A2 contains no hyphen, FIND returns an error, and that error flows through the formula. Check that the source data follows the expected pattern before filling a formula down a large list.
For text split around delimiters, newer Excel releases also offer functions designed specifically for that job. TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. They can be easier to read when the goal is simply to return text before or after a delimiter. LEFT, RIGHT, MID, and FIND remain useful when you need compatibility with older Excel versions or need precise control over character positions.
Using the Functions With Variable-Length Text
Fixed counts are not always enough. If you need the final character from entries of different lengths, RIGHT can still work without specifying a count. With text in A2, =RIGHT(A2) returns its last character. To return the last three characters of a value, use =RIGHT(A2,3); the source value must have enough characters for the result you expect.
You can also calculate a character count instead of typing one by hand. For example, if a cell contains a prefix of known length followed by a variable-length remainder, LEN can help determine how many characters to take. Consider a code in A2 where the first four characters are always a fixed prefix and you want everything after them. With that layout, =RIGHT(A2,LEN(A2)-4) returns the text after the first four characters. If the value is shorter than four characters, the requested count becomes negative and Excel returns an error, so the pattern is appropriate only when the input length meets that condition.
When the separator itself is more reliable than a fixed prefix length, locating it with FIND is often clearer. The best choice depends on the data rule: use positions when the format is fixed, and use a delimiter when its location varies but the delimiter is dependable. A formula cannot infer the meaning of a code; it can only apply the pattern you specify.
Common Errors and Misunderstandings
Most problems come from counting positions incorrectly, supplying an invalid count, or expecting the returned text to behave like a number. Review these points when a formula gives an unexpected result:
- MID starts at 1. The first character is position 1, not position 0. A starting position below 1 produces a
#VALUE!error. - Character counts cannot be negative. A negative num_chars value in LEFT, RIGHT, or MID produces an error. MID also requires a valid starting position.
- Long counts do not necessarily cause an error. If the requested count exceeds the available characters, Excel returns the characters that are available.
- Text results may not be numbers. RIGHT can return
047as text. That preserves formatting such as a leading zero, but it may need conversion before arithmetic or numeric comparisons. - Spaces count as characters. A leading or trailing space affects positions and counts even when it is hard to see. TRIM can remove ordinary extra spaces, but it does not remove every possible nonprinting character.
- Hard-coded positions rely on consistent data. If a prefix changes length, a MID formula using a fixed starting number can return the wrong characters without showing an error.
If a result looks wrong, check the source cell in the formula bar, count punctuation and spaces, and confirm that your formula points at the intended cell. An error is sometimes useful evidence that an input does not match the expected format; hiding it immediately can make inconsistent records harder to find.
Choosing Between Formulas and Other Tools
Use LEFT, RIGHT, or MID when you want a formula result that updates as the source cell changes. Formulas are particularly useful in a table of repeated records: enter the formula beside the first record, then fill it down for the remaining rows. If the source text changes, the extracted result recalculates.
For a one-time split of a column, Excel’s Text to Columns tool may be more convenient. Select the data, then choose Data > Text to Columns and follow the wizard to split by a delimiter or fixed width. This changes the selected data into separate columns rather than creating a live formula linked to the original value, so keep a copy of the source if you need it.
For repeated imports or more involved cleanup, Power Query can provide a refreshable transformation process. For simple extraction, however, a short formula is often easier to inspect and maintain. Choose based on whether you need a live cell-by-cell result, a one-time split, or a repeatable import workflow.
Practical Tips for Building Reliable Formulas
Before applying an extraction formula to hundreds of rows, test it on several representative examples: the shortest value, the longest value, and any entries with unusual punctuation or missing parts. Check that the result is correct for each variation rather than relying on a single well-formed row.
- Keep the original column intact until you have checked the extracted results.
- Use a clear header for the result column, such as Year or Suffix, so readers know what the formula returns.
- When copying a formula, verify that cell references adjust as intended. A2 normally changes to A3 on the next row; dollar signs can lock a reference when you specifically need it fixed.
- Document the pattern behind a fixed position. A formula may be mathematically correct but confusing if a future user does not know why it extracts characters 5 through 8.
- Check for leading zeros and spaces before converting extracted text to another data type.
For a formula result that appears blank, confirm whether the chosen starting position is beyond the end of the text or whether the cell itself contains an empty string. For unexpected results, inspect the exact characters and their positions rather than judging only by how the cell is displayed.
Frequently Asked Questions
What is the difference between LEFT and RIGHT in Excel?
LEFT takes characters starting at the beginning of text, while RIGHT takes characters starting at the end. Both accept a text value and an optional number of characters; if the count is omitted, each returns one character.
How do I use MID in Excel?
MID needs a source text, a starting position counted from the left, and the number of characters to return. The first character is position 1. Include punctuation and spaces when counting positions.
Can LEFT, RIGHT, and MID return numbers?
They return text extracted from the source. A result made up of digits may look numeric, but can remain text, and a leading zero is retained as text. Convert it only if you need numeric behavior and do not need that zero as part of an identifier.
Why does my MID formula return #VALUE?
Common causes include a starting position below 1, a negative character count, or an invalid value passed into an argument. If the formula uses FIND, the error can also mean the separator was not found in the source cell.
Can I use these functions to extract text before or after a delimiter?
Yes. You can combine MID or LEFT with FIND to locate a delimiter, provided the formula matches the structure of your data. In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER are also options for direct delimiter-based extraction.
Related
- Excel Data Validation: Create Rules and Drop-Down Lists
- How to Set Data Validation in Excel for Reliable Entries
- Detect Circular References in Excel: Step-by-Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Test If a String Contains Text: Formula ExamplesTo test whether a cell contains a particular string in Excel, use =ISNUMBER(SEARCH("apple",A2)), replacing “apple” with the text…
- How to Use CONCAT in Excel to Join TextTo join text in Excel, enter a formula such as =CONCAT(A2," ",B2) in an empty cell, replacing A2…
- Replace an Excel Cell Value: Methods, Formulas, and TipsTo replace a value in one Excel cell, select the cell, type the new value, and press Enter;…
- Excel LEN Formula Examples: Count Text in CellsIn Excel for Microsoft 365, use =LEN(A2) to count the characters in cell A2, including spaces and punctuation.…
- Excel INDEX Function Examples: Useful Formulas and UsesExcel’s INDEX function returns a value—or a reference to a value—from a range based on its row and…
- Text Split in Excel: Methods, Formulas, and Common FixesTo split text in Excel, select the cells and choose Data > Text to Columns to separate values…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.