LEFT, RIGHT and MID Formulas in Excel: How to Use Them

Formulas & functions · Updated November 18, 2026

Use LEFT to return characters from the start of a text value, RIGHT to return them from the end, and MID to return characters from a position you choose. In each case, select the source cell and specify how many characters to extract; MID also needs a starting position. These functions are useful for separating fixed-length codes, IDs, and other text stored together in a cell.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, or a recent Excel version. The LEFT, RIGHT, and MID functions are available in older Excel versions too.
  • A worksheet with text to split. For the examples below, enter INV-2026-047 in cell A2.
  • Space beside the source column for the extracted results. The formulas leave the original value unchanged.

No add-ins or special settings are required. Excel for Mac and Excel for the web use the same functions and formula structure. Depending on your regional settings, Excel may display semicolons instead of commas between formula arguments; if a comma-based example is rejected, check the separator your Excel installation expects.

Step-by-Step

  1. Prepare a simple example. Enter the heading Code in A1 and type INV-2026-047 in A2. This code has three parts: the first three characters are the type, the four characters after the first hyphen are the year, and the last three characters are the reference number. Add headings such as Type, Year, and Reference in B1, C1, and D1 so you can identify the results.

    In a real sheet, use your own source cell instead of A2. Check that the source is stored as text in the sequence you expect. These functions extract by character position; they do not understand what a hyphen or a particular section means.

  2. Extract characters from the left with LEFT. LEFT reads from the first character and continues for the number you specify. With INV-2026-047 in A2 and the three-character type in B2, enter:

    =LEFT(A2,3)

    The result is INV. The 3 tells Excel to return the first three characters, counting from the left. Replace A2 with the address of your own source cell and replace 3 with the number of leading characters you need. If you omit the character count, LEFT returns one character, which is often not enough for a code.

  3. Extract characters from the right with RIGHT. RIGHT counts from the last character and works backward. With the code in A2 and the three-character reference number in D2, enter:

    =RIGHT(A2,3)

    The result is 047. This is handy when the part you need always has a fixed number of characters at the end, even if the earlier part of the text varies. If the reference has a different fixed length, change 3 to that length. RIGHT returns text, so the leading zero in this example remains visible in the result.

  4. Extract characters from the middle with MID. MID starts at a position you supply, then returns a chosen number of characters. With the code in A2 and the four-character year in C2, enter:

    =MID(A2,5,4)

    The result is 2026. The second argument, 5, is the starting character position: I is position 1, N is 2, V is 3, the hyphen is 4, and the year begins at 5. The final argument, 4, tells Excel to return four characters. Unlike LEFT and RIGHT, MID requires both a start position and a character count.

  5. Check each result against the original. Compare the extracted parts with A2 before using them elsewhere. For this example, B2 should show INV, C2 should show 2026, and D2 should show 047. If a result is shifted, count the characters in the original from 1, including spaces, hyphens, slashes, and other punctuation. MID positions are based on every character, not just letters or digits.

    It is also useful to try the formulas on a few different source values. A formula can appear correct on one row but return the wrong section when another row has a different-length prefix or an extra space. The functions do exactly what their positions and counts specify; they do not adjust automatically to a changed layout.

  6. Fill the formulas down for more rows. If your next code is in A3, select B2:D2 and use the fill handle—the small square at the lower-right corner of the selection—to copy the formulas down. Excel adjusts the references to A3, A4, and so on. You can also copy the cells and paste them into the rows below. Inspect several results, especially the first and last rows, to confirm that every source value follows the same pattern.

    If your data is formatted as an Excel table, enter the formula in the first row of the result column. Excel will usually fill the formula down that table column automatically. If it does not, copy the formula down as described above and check that the formula refers to the source cell on the same row.

  7. Use the result as text or convert it only if needed. LEFT, RIGHT, and MID return text, even when the extracted characters look like a number. That is useful for IDs such as 047, where the zero matters. If you need to do arithmetic with an extracted value, convert it to a number separately and check that doing so will not remove meaningful leading zeros. Keep the original column until you have verified the results and no longer need it.

The argument order is the main difference to remember. LEFT and RIGHT take the source text and an optional character count. MID takes the source text, a starting position, and a character count. The square brackets often shown in help descriptions mean an argument can be omitted; they are not characters to type into a formula. For everyday extraction, enter the function directly in a worksheet cell, press Enter, and use the formula bar to review it if the output surprises you.

Common Mistakes

  • Counting from zero or overlooking punctuation. Excel character positions start at 1. A hyphen, slash, space, or period takes up a position just like a letter. When MID starts one character too early or late, write out the text with numbered positions before changing the formula.
  • Using the wrong direction. LEFT starts at the beginning and RIGHT starts at the end. A value such as an ID with a fixed suffix may be easiest with RIGHT, while a fixed prefix is easiest with LEFT. MID is the choice when the desired section begins at a known position between them.
  • Expecting the functions to find a delimiter. These functions use fixed character counts and positions. If a hyphen moves because one code has a longer prefix, the same MID position may extract different characters. A fixed-position formula is only dependable when the source format is consistent.
  • Including a space in the count by accident. Spaces are characters too. If the source contains a space after a name or code, a LEFT or MID result may include it. The result can look as if it has an extra gap, particularly when it is joined to other text later.
  • Confusing displayed formatting with stored content. A numeric cell formatted to display leading zeros may not contain those zeros as text. Extraction works on the stored value, not necessarily the way the cell looks. For identifiers, store the source as text when the zeros are part of the actual ID.

Tips

  • Keep a small test set. Try a short value, a typical value, and the longest or shortest expected value. This reveals whether the character count fits the full range of your data before you fill a formula through hundreds of rows.
  • Label output columns clearly. Names such as Prefix, Year, or Last 3 characters make it easier to review the worksheet and spot a formula copied into the wrong column.
  • Use the formula bar when checking references. Click a result cell and look at the formula bar to confirm the source address, start position, and count. This is especially helpful after copying formulas between columns or worksheets.
  • Preserve source data while validating. Avoid overwriting the original text until you have compared the extracted columns with the source. If a pattern changes later, keeping the original lets you correct the formulas without rebuilding the data.
  • Pay attention to text versus numbers. Excel treats the output as text. This protects codes with leading zeros, but a text result may not behave like a number in a sum or numeric comparison. Decide whether the value is an identifier or a quantity before converting it.

Troubleshooting

If Excel displays an error, first check the formula punctuation and the cell reference. In some regional configurations, Excel expects semicolons between arguments rather than commas. Use the separator accepted by your installation. Also check that the parentheses are balanced and that each required argument is present, particularly the starting position and count in MID.

If MID returns the wrong section, count from the first character of the source cell, not from the first meaningful letter or digit. Include spaces and separators in the count. If the source pattern is not consistent from row to row, the issue is not a calculation error: a fixed position cannot reliably identify a section that moves. Sort or inspect the affected values, determine the actual pattern, and use an approach suited to that variation rather than changing the count to fit only one row.

If the result is blank, check whether the starting position is beyond the end of the source text or whether the referenced source cell is blank. If a result is shorter than expected, the requested range may extend past the end of the available text. If you see #VALUE!, review the numeric arguments: a start position below 1 or a negative character count is invalid. A count of zero returns no characters, so make sure the count has not accidentally been set to 0.

If a leading zero disappears, check the extracted result and the source cell separately. The extraction functions return text, but if the original value was entered as a number, Excel may already have discarded an initial zero before the formula runs. A number format that merely displays extra zeros does not add those characters to the stored value. Re-enter or import the ID as text if the zeros are part of the code.

Frequently Asked Questions

What is the difference between LEFT, RIGHT, and MID?
LEFT returns characters starting at the beginning of the text, RIGHT returns characters starting at the end, and MID returns characters from a position you choose inside the text. Pick the function based on where the part you want sits.

Can I use these functions with numbers?
Yes. Excel can use a numeric cell as the text input, and the functions return text. Keep in mind that formatting is not the same as stored content: a number displayed with leading zeros may not contain those zeros for the function to extract.

What happens if I request more characters than the cell contains?
LEFT and RIGHT return the available characters. MID returns the characters available from its starting position; if that position is past the end of the text, the result is empty. This usually does not cause an error by itself.

Can LEFT, RIGHT, or MID split text at a changing hyphen or space?
Not by themselves. Their counts and positions are fixed numbers, so they work best when the text format stays consistent. When a separator moves, a fixed-position formula may return the wrong characters; inspect the data pattern and choose a method that can account for the variation.

Do these formulas work in Excel for Mac and Excel for the web?
Yes. LEFT, RIGHT, and MID use the same function names and argument order in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. Formula separators can vary with regional settings, so follow the separator Excel accepts in your environment.

Why does MID need two numbers?
MID needs a starting character position and a number of characters to return. The first number tells Excel where to begin, and the second tells it how far to read. Count every character in the source, including spaces and punctuation.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.