Table of Contents
To get the MID from the right in Excel, you can use the formula MID(text,start_num,num_chars) where text is the cell containing the text you want to extract, start_num is the number of characters from the right that you want to start from, and num_chars is the number of characters you want to extract. This formula will return the specified number of characters from the right of the given text.
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.