Excel MID function is used to extract a specific number of characters from a text string, starting from a specified position. This function is very useful when you need to extract a specific part of a string or text value, such as the first name, last name, or a code from a larger text string.
Excel MID Function Summary
Excel’s MID function is a powerful tool for extracting specific text from a larger string. MID allows you to select a specific number of characters from a text string, starting at a specific position that you define. This makes it easy to extract only the information you need from a larger data set.
MID Function Purpose
The purpose of the Excel MID function is to extract a specified number of characters from a text string, starting at a specified position.
MID Function Arguments
- Text: The text string from which you want to extract the characters.
- Start_num: The position of the first character that you want to extract.
- Num_chars: The number of characters that you want to extract from the text string.
MID Function Return value
The Excel MID function returns the specified number of characters from the text string, starting from the specified position.
MID Function Syntax
=MID(text, start_num, num_chars)MID Function Examples
The MID function in Excel allows you to extract a specific number of characters from a text string, starting at a specified position. It’s a useful function for manipulating text data in various ways. Here are a few examples of how you can use the MID function in Excel.
Extracting a specific number of characters from a text string
Suppose you have a list of product IDs that are all 6 characters long, and you want to extract the first 3 characters to identify the product.
Here’s how you can use the MID function to do that: Formula:
=MID(A1,1,3)
Explanation: This formula tells Excel to extract 3 characters from the beginning of the text string in cell A1. The “1” specifies the starting position of the extraction, and the “3” specifies the number of characters to extract.
Extracting a variable number of characters from a text string
Suppose you have a list of email addresses in which the first name is followed by a dot and a last name, and you want to extract just the last name.
Here’s how you can use the MID function to do that with the following formula:
=MID(A1,FIND(".",A1)+1,LEN(A1)-FIND(".",A1))
Explanation: This formula tells Excel to find the position of the dot in the text string in cell A1 using the FIND function, add 1 to that position (to skip over the dot), and then extract the remaining characters using the LEN and MID functions. The LEN function calculates the length of the entire text string, and the subtraction of the dot position from that length gives us the number of characters to extract.
Extracting a specific number of characters from the middle of a text string
Suppose you have a list of product descriptions that all start with a product code in parentheses, followed by a description in brackets. You want to extract just the description, which is always 15 characters long and starts 9 characters after the opening bracket.
Here’s how you can use the MID function to do that with the following formula:
=MID(A1,FIND("[",A1)+9,15)
Explanation: This formula tells Excel to find the position of the opening bracket in the text string in cell A1 using the FIND function, add 9 to that position (to skip over the product code and the opening bracket), and then extract the following 15 characters using the MID function.
Extracting First Name from a Full Name Using the Excel MID and SEARCH Functions
Let’s say we have a list of full names in a column, and we want to extract only the first names into a separate column. Here’s how we can do it using the MID, SEARCH, and LEFT functions:
=MID(A1,1,SEARCH(" ",A1,1)-1)
Breakdown of the function:
- MID: This is the function that we are using to extract a portion of the text from B17.
- A1: This is the cell reference that contains the full name that we want to extract the first name from.
- 1: This is the starting position for the text that we want to extract. Since we want to extract the first name, we will start at the first character of the text.
- SEARCH(” “,A1,1)-1: This is the number of characters that we want to extract. The SEARCH function is used to find the position of the first space character in the text string, which represents the end of the first name. The -1 is used to exclude the space character from the extracted text.
Extracting Surname Name from a Full Name
Let’s say we want to extract the last name we would need the following formula:
=MID(A1,SEARCH(" ",A1)+1,LEN(A1)-SEARCH(" ",A1))
Breakdown of the function:
- MID: This function extracts a specific number of characters from a text string, starting at a specified position.
- A1: This is the cell containing the full name.
- SEARCH(” “,A1)+1: This part of the formula searches for the position of the first space in the full name using the SEARCH function, and then adds 1 to the result to move the starting position of the MID function to the character immediately following the space, which is the first character of the last name.
- LEN(A1)-SEARCH(” “,A1): This part of the formula calculates the number of characters in the last name by subtracting the position of the first space from the total length of the full name using the LEN function.
So essentially, the MID function is extracting a substring of the full name. Starting at the first character of the last name and ending at the end of the full name.
The starting position is calculated by adding 1 to the position of the first space in the full name, and the number of characters to extract is calculated by subtracting the position of the first space from the total length of the full name.
Excel MID Function Notes
- The start_num argument must be a positive number.
- The num_chars argument must be a positive number or zero. If num_chars is zero, the function returns an empty string.
- If start_num is greater than the length of the text string, the function returns an empty string.