No: Excel’s MID function always counts characters from left to right. To take a fixed number of characters from the end of a cell, use RIGHT; if you specifically need MID, calculate its starting position with LEN.
Does Excel MID Start From the Right?
MID does not have a setting or argument that reverses its direction. Its syntax is MID(text, start_num, num_chars): it starts at a numbered character in the text and returns the requested number of characters moving to the right. The first character is position 1, the second is position 2, and so on.
For example, if A2 contains Order-2026-5831, MID cannot be told to begin at the right edge. You can, however, work out the position of the ending characters using LEN. For most everyday requests—such as getting the final four characters—RIGHT is simpler and easier to read.
The useful distinction is between the result you want and the function you think you need. If the goal is simply “return the last few characters,” RIGHT is designed for that. If another part of a formula requires MID, combining MID with LEN produces a substring whose position is calculated relative to the end.
How MID Calculates Character Positions
MID counts from the beginning of its text argument. The start_num argument is a position, not a count backward from the end. The num_chars argument is how many characters to return, including the character at the starting position.
Suppose a cell contains ABCD123456. Its length is 10 characters. The final three characters begin at position 8, so a MID formula that returns them must start at position 8 and request 3 characters. LEN is helpful because it calculates the text length rather than requiring you to count characters manually. That matters when cell contents vary in length.
Spaces and punctuation count as characters too. In AB-123, the hyphen occupies a position just like a letter or digit. A trailing space also counts and may therefore appear in a result that seems to be missing a character. Excel’s character positions are based on the text stored in the cell, not on how the value looks when formatted.
Use RIGHT for a Fixed Number of Ending Characters
For a suffix with a known width, RIGHT is usually the best choice. It has the form RIGHT(text, num_chars) and counts from the end, which matches the request directly. It can return the last digit of an identifier, a fixed-width product code, or the final four characters of a file name.
With the text Order-2026-5831 in A2, this formula returns 5831:
With the order code in A2, return its final four characters; the result is 5831.
=RIGHT(A2,4)
RIGHT is also easier for a colleague to understand at a glance than a position calculation. If the requested number of characters is stored in another cell, use that cell as the second argument. This lets a formula adapt when the desired suffix width changes.
With the text in A2 and the desired character count in B2, where B2 contains 4, the result is 5831.
=RIGHT(A2,B2)
RIGHT returns text. A result that looks like a number may still be text, which can affect arithmetic, sorting, or matching. Convert it only if a numeric value is genuinely needed; conversion will remove leading zeros. For the same order code in A2, this formula returns the number 5831, not the text string 5831.
=VALUE(RIGHT(A2,4))
Use MID with LEN When You Need MID Specifically
To make MID return the final characters, calculate the starting position as the total length minus the number of characters to return, plus 1. In other words, for a text string with length L and a requested result of n characters, the start position is L − n + 1. LEN supplies L, so the formula adjusts automatically for different text lengths.
With Order-2026-5831 in A2, LEN returns 15. The starting position for four characters is 15 − 4 + 1, or 12. MID then returns the four characters at positions 12 through 15: 5831.
With the order code in A2, return its last four characters using MID; the result is 5831.
=MID(A2,LEN(A2)-4+1,4)
This result is equivalent to =RIGHT(A2,4), but the MID version can be useful when you are adapting a formula that already uses MID or when the start position is part of a larger calculation. It is not inherently more capable than RIGHT for this particular task. Use the simpler function unless there is a reason to keep MID.
You can make both the distance from the end and the number of returned characters adjustable. For example, imagine you want three characters beginning at the fifth character from the right and proceeding toward the end. The fifth character from the right is at position LEN minus 5 plus 1. In ABCD123456, that position is 6, and the next three characters are 234.
With ABCD123456 in A2, the right-relative starting position in B2 set to 5, and the number of characters in C2 set to 3, the result is 234.
=MID(A2,LEN(A2)-B2+1,C2)
This setup treats B2 as the position, counted from the right, of the first character to return, and C2 as the number of characters to return moving rightward. That definition is important: saying “start five characters from the right” can be ambiguous unless you also specify the direction and length of the extracted segment.
Extracting a Segment After a Delimiter
A different request is to return the text after the final hyphen, underscore, or other separator. If the part you want has a variable length, counting a fixed number of ending characters may not work. In Microsoft 365 and Excel 2024, TEXTAFTER can search for a delimiter; a negative instance_num makes it search from the end of the text.
With INV-2026-5831 in A2, this formula returns the text after the last hyphen, 5831:
With the identifier in A2, return the text after its final hyphen; the result is 5831.
=TEXTAFTER(A2,"-",-1)
This approach is often more robust than RIGHT when the suffix length varies but the separator is consistent. It also states the rule more clearly: return the text after the last hyphen, rather than return the last four characters. TEXTAFTER is available in Microsoft 365 and Excel 2024; workbooks opened in older Excel versions may not calculate it.
If there is no delimiter, TEXTAFTER normally returns an error unless you provide its optional if_not_found argument. Before building a formula around a separator, check whether every record actually contains one and whether the delimiter itself can appear elsewhere in the text.
Choosing the Right Formula
Choose a method based on how the data is structured and what the result means. A short decision guide is:
- Use RIGHT when you need a known number of characters from the end.
- Use MID with LEN when the required output must use MID or when you need its position-based behavior.
- Use TEXTAFTER when you need everything after a separator that may occur at a variable position, provided your Excel version supports it.
- Keep the result as text when leading zeros or text comparisons matter; convert it to a number only when calculations require a numeric value.
For example, if invoice IDs always end in four digits, RIGHT expresses that fixed-width rule. If the ID is built from sections separated by hyphens and the final section can have two, four, or six characters, extracting after the last hyphen describes the structure more accurately. If the suffix width changes by row and is stored in a column, pass that row’s width to RIGHT or use it in the LEN-based starting-position calculation.
A formula cannot determine the meaning of the characters by itself. If a code sometimes ends in a checksum, a variable suffix, or a space, the spreadsheet needs an explicit rule for what counts as the desired ending portion. Establishing that rule first prevents a technically correct formula from returning the wrong piece of data.
Common Errors and Edge Cases
Check the requested length and the underlying cell contents when a result looks wrong. If the requested number is greater than the text length, RIGHT returns the whole text. MID returns characters from its start position through the end if fewer characters remain than requested. A zero character count returns an empty string; a negative character count produces a #VALUE! error.
MID also needs a valid starting position. A start_num below 1 returns #VALUE!. In the LEN-based pattern, a requested length of zero can make the calculated start position equal to the end of the text plus one. That is another reason to validate adjustable inputs when users can change them.
To return an empty result instead of an error when a cell has fewer than four characters, use an IF check. This example assumes A2 holds the text and requests four final characters. If A2 contains 5831, the result is 5831; if it contains only 83, the result is blank.
=IF(LEN(A2)>=4,RIGHT(A2,4),"")
Also check for invisible spaces. A value that appears to end in 5831 might actually have a trailing space, so its final four characters could include that space. If the data should not contain extra spaces, TRIM may be appropriate before extraction, but remember that TRIM also reduces repeated internal spaces to single spaces. Do not use it automatically if spacing is meaningful.
Finally, cell formatting does not change the stored text. A number displayed with leading zeros because of a custom number format is still a numeric value underneath. Text-extraction formulas work with the cell’s value as Excel represents it, so a displayed code such as 0017 may not behave like a four-character text string. If leading zeros are part of the identifier, store the identifier as text or construct the required text representation deliberately.
Version and Platform Notes
MID, RIGHT, and LEN are long-standing worksheet functions available in desktop Excel for Windows and Mac, as well as Excel for the web. The formulas shown here use commas between arguments, as requested for standard English-language Excel syntax. Some regional settings use semicolons as formula separators; if Excel rejects a pasted formula, your installation’s list separator may differ.
TEXTAFTER is the version-sensitive option in this discussion: it is available in Microsoft 365 and Excel 2024, but not in every older perpetual Excel release. The RIGHT and MID-with-LEN approaches do not depend on that newer function. The basic formula behavior is the same on Windows, Mac, and the web; there is no special ribbon command required to extract the characters.
Frequently Asked Questions
Can I make MID count backward?
No. MID always counts from the first character toward the right. Calculate a starting position with LEN, or use RIGHT when the goal is to return a fixed number of ending characters.
What is the simplest formula for the last three characters?
Use =RIGHT(A2,3) when the source text is in A2. RIGHT counts from the end, so there is no need to calculate the starting position yourself.
Why does MID return the wrong characters after I use LEN?
Check the plus-one in the starting-position calculation and confirm the requested character count. For n characters from the end, the start is LEN(text) − n + 1. Also look for spaces or punctuation that count toward the text length.
Can RIGHT return the last word instead of the last characters?
RIGHT needs a character count, so it does not identify word boundaries by itself. For data with a reliable separator, a delimiter-based function such as TEXTAFTER can return the portion after that separator. The exact formula depends on whether the separator is a space, hyphen, or another character.
Why did my extracted number lose its leading zero?
Excel treats numeric values and text differently. Converting extracted text with VALUE removes leading zeros because they are not significant in a number. Keep identifiers as text when zeros are part of the code.
Related
- Excel Data Model Measures: Add, Format, and Use One
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Wildcard Text Search: Formulas and Examples GuideTo check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when…
- Excel Filter Not Showing All Data? Find the Cause FastExcel filters usually miss rows because the filter range stops before the full dataset, or because the values…
- Excel Formula for Start of Month: Dates, Examples & TipsTo return the first day of the month for a date in cell A2, use =DATE(YEAR(A2),MONTH(A2),1). If A2…
- SUMIF Function Examples: Text, Numbers, Dates in ExcelExcel’s SUMIF function adds numbers that meet one condition. With product names in A2:A6 and sales in B2:B6,…
- How to Use the PMT Function in Excel for PaymentsUse Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a…
- Microsoft Excel SUMIF Examples and How to Use ThemIn Microsoft Excel, SUMIF adds values that meet one condition. For example, with categories in A2:A6 and amounts…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.