How To Use the MID Function in Excel (Plus Tips)
By Indeed Editorial Team
Published April 8, 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
If you have long strings of text in an Excel document, you may want to extract a small portion of them. The MID function allows you to produce specific characters from a string of text. Understanding how to use the MID function in Excel can help you better organize a large data set. In this article, we explain how to use the MID function in Excel and provide tips for applying this formula.
Related: 14 Ways To Get Organized at Work
What is the MID function in Excel?
The MID function in Excel is a formula that yields a certain number of characters from a long string of text. This function consists of three inputs that you provide. The first component is the cell that contains the initial text string. The second and third components are the starting character and the number of characters you want to extract.
When can the MID function be useful?
This formula is useful if you want to shorten strings of text. For example, an Excel user may have a long list of employee names. The user may want to divide the first and last names into different columns. They can do so by applying the MID function to produce each employee's first and last name. Because the first and last names are likely to vary in character length, the Excel user may enter the formula for each cell individually.
How to use the MID function in Excel
Here's a list of steps on how to use the MID function in Excel:
1. Enter all your initial strings of text into individual cells
The first step is to enter all of your initial strings of text into individual cells. One of the easiest ways to do this is to enter each string of text into a column. This information may be employee names, course names or any other kind of text data that you want to shorten.
Related: What Are the Primary Types of Data?
2. Initiate the MID formula
The next step is to initiate the MID formula. Locate the cell that contains your first string of text and start typing the MID formula into the cell to the right of it. Type an equal sign and follow it with the word "MID." Insert an open parenthesis.
3. Add the 3 inputs for the MID formula
After you insert an open parenthesis, you can add the text component by typing out the cell name. Alternatively, you can click on the cell that contains your original string of text. Insert a comma once you input the cell name. Add a space and designate your starting number. The starting number is the location of the first character you want to retrieve.
Insert another comma and add a space. The final component you can input into the MID formula is the number of characters you want to extract from the starting number. For example, let's say your initial cell has the text "Mr. Jones." If you designate the starting number as 5 and the number of characters as 5, the formula yields a result of just "Jones."
4. Close the MID formula
Once you enter all of the essential inputs, you may close the MID formula. Type a closing parenthesis and hit the "Enter" key on your keyboard. You can notice that Excel fulfills the command by extracting the specific number of characters you requested from your starting number.
Example of the MID function in Excel
Here's an example of the MID function in Excel:
A manager of an athletic shoe wear company copies information of product names from a supplier's inventory sheet in Excel. All of the product names have the phrase "Product Name" in front of them. The manager wants to remove these two words from all the product names to simplify the data's readability.
They enter the formula "=MID(A1, 14, 25)" into cell B1. The "14" component of the formula causes the new cell to produce all characters after the phrase "Product Name. It also excludes the space after "Name." The "25" component ensures that all characters after each "Product Name" are fully visible. The manager copies this formula to cells B3 through B10.
AB1Initial Product Name**New Product Name**2Product Name RevolutionRevolution3Product Name FiascoFiasco4Product Name StrikeStrike5Product Name RealmRealm6Product Name AnchorAnchor7Product Name ExpressExpress8Product Name FusionFusion9Product Name AlleyAlley10Product Name FoxFox## Tips for using the MID function in Excel
Here are some tips for using the MID function in Excel:
Copy the formula for other cells automatically
Once you type in the MID formula once, you can copy it to multiple cells at the same time. Hover over the cell that currently contains the MID formula. Look for a large black plus sign to appear. Click on this plus sign and drag your cursor downward to apply the formula to other cells in your Excel worksheet.
Only use cell designations for the "text" input
After you type the "=MID(" portion of the MID formula, be sure to input the text using a cell designation. Typing the actual text in this area produces an error. You can input the specific cell by typing in the cell name or clicking on the cell that contains your original string of text.
Only provide inputs that are 1 or greater
Inputting 0 for either the starting number or the number of characters produces an empty cell or a #VALUE! error. Inputting any negative number produces similar results. Ensure that you always enter a value of 1 or greater for both the starting number and the number of characters inputs.
Consider choosing a large number for the "number of characters" element
When you're choosing the number for the "number of characters" element of the MID formula, consider selecting a large number. This can be helpful if you want to omit the first part of a string of text but include the remaining characters. For example, choosing a number like "100" for the "number of characters" input can ensure that the new cell doesn't omit any of the remaining characters after the starting number.
Please note that none of the companies mentioned in this article are affiliated with Indeed.
Explore more articles
- 8 Resource Management Best Practices (With FAQs)
- What Is a robots.txt File? (And When To Use One)
- How To Connect Bluetooth Headphones to a Mac In 5 Steps
- What Is Omnichannel Customer Service? (Plus Best Practices)
- How To Insert an Image in Excel (With Tips)
- What Is Incident Response and How Does It Benefit Organizations?
- What Is Database Marketing? (Definition and How To Use It)
- 11 IT Asset Management Software Programs (With Key Features)
- 10 Expert Cross-Selling Tips for Sales Professionals
- Anchor Text: Definition, Types and Strategies for SEO
- How To Do Instagram Live Effectively: A Step-by-Step Guide
- Parts Counterperson Skills: Definition and Examples