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:
=RIGHT(REPLACE(A1,LEN(A1)-3+2,LEN(A1),""), 4)
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:
=RIGHT(REPLACE(A2,LEN(A2)-3+2,LEN(A2),""), 4)
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:
- Mavericks
The 3rd position from the right is the letter c:
- Mavericks
We then extract the 4 characters starting from this position:
- Mavericks
The result is eric.
The formula repeats this process for each name in the Team column.
Additional Resources
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”