Excel MID function
In Excel workbook, when you want to extract part of text from a cell value based on the location and length, the MID function can help you to solve this task. MID function is used to find and return a specific number of characters from the middle of given text string.
The syntax for the MID function in Excel is:
Return the specific characters from the middle of text string.
Example 1: Basic uses of the MID function
For example, I want to extract 10 characters which begins at the third character from the list of text strings. Please enter this formula into a blank cell:
Example 2: Use MID function to extract first and last names from full names
We can also use the MID function to extract the first and last names from the full names, please do as follows:
Extract the first names from full names:
Please enter or copy the below formula into a blank cell where you want to output the result, and then drag the fill handle down to the cells you want to apply this formula, and all the first names have been extracted as following screenshot shown:
Extract the last names from full names:
Pull the last names from the full names, please apply the below formula:
Example 3: Use MID function to extract substrings between two delimiters
Sometimes, you may want to extract the text between the two dashes as below screenshot shown, to solve this task, combining the MID and FIND functions can help you.
Please copy the following formula into a blank cell:
And then, drag the fill handle down to fill this formula to other cells you need. And you will get the result as below screenshot shown:
1. You can change the delimiter “-” to other separators as you need.
2. The above formula is applied to extract the text between the first and second occurrences of the specific delimiter.
- Excel RIGHT Function
- RIGHT function is used to return the text from right of the text string.
- Excel SEARCH Function
- The SEARCH function can help you to find the position of a specific character or substring from the given text string.
- Excel REPLACE Function
- The REPLACE function in Excel can help you to find and replace characters based on given location from text string with a new text.