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.
Syntax:
The syntax for the MID function in Excel is:
Arguments:
- text: Required. The text string that you want to extract characters from.
- start_num: Required. The location of the first character that you want to extract.
- num_chars: Required. The total number of characters you want to extract.
Notes:
- 1. If start_num is greater than the length of the original text, it will return an empty string ("").
- 2. If start_num is less than 1, MID function returns the #VALUE! error value.
- 3. If num_chars is negative, MID function will also return the #VALUE! error value.
- 4. If the sum of start_num and num_chars greater than the total length of the original text string, the MID function will return the characters from start_num to the last character.
Return:
Return the specific characters from the middle of text string.
Examples:
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:
Notes:
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.
More Functions:
- 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.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
