The MID function in Excel allows you to extract a specific number of characters from a string based on a starting position on the left side of the string.
However, you can use the following formula to replicate the MID function and extract a specific number of characters using a starting position on the right side of the string:
=RIGHT(REPLACE(A1, LEN(A1)-start_from_right+2, LEN(A1),""), num_characters)
For example, you can use the following formula to extract the 4 middle characters in cell A1 starting 3 positions from the right:
The following example shows how to use this formula in practice.
Example: Using MID From Right in Excel
Suppose we have the following list of basketball team names in Excel:
We can type the following formula into cell B2 to extract the 4 middle characters from the team name in cell A2 starting 3 positions from the right:
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays the 4 middle characters from each team name starting 3 positions from the right.
For example, consider the first team name:
The 3rd position from the right is the letter c:
We then extract the 4 characters starting from this position:
The result is eric.
The formula repeats this process for each name in the Team column.
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use MID Function to End of String
Excel: How to Use MID Function for Variable Length Strings
Excel: A Simple Formula for “If Not Empty”