How to Use the Excel MID Function GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

How to Use the Excel MID Function

This function helps you extract text from a cell

By Aaron Peters Aaron Peters Writer Villanova University Aaron Peters is a writer with Lifewire who has 20+ years experience troubleshooting and writing about consumer and business technology. His work appears in Linux Journal, MakeUseOf, and others. lifewire's editorial guidelines Updated on July 20, 2022 Tweet Share Email Tweet Share Email

In This Article

Expand Jump to a Section What is the MID Function The MID Function in Action MIDB the More Complicated Sibling

What to Know

MID function: =MID(A1,17,4). Examples A1 = target, 17 = offset, and 4 = length of extraction.Select target cell > input MID function > set values for function target, character offset, and extraction length. This article explains how to use the MID function in Excel for Microsoft 365, Excel Online, and Excel 2016 and 2019 for both Windows and macOS.

What is the MID Function

The MID function allows you to extract text from the middle of a string. Like other functions, you use it by entering a formula in a cell that contains the function and it's parameters. The following represents what a typical MID formula looks like: =MID(A1,17,4)

The formula's parameters break down as follows: The first parameter ("A1" in the example above): This is the target of the function, and should represent some text. One option is for you to put the text directly in this spot, surrounded by quotes. But more likely it'll be a cell containing said text. Note that if the cell contains a number, it will be treated like text.The second parameter ("17" in the example above): This is the offset, or the number of the character on which you want to start the extraction, inclusive. This should be a whole number (i.e. without any quotes) greater than one. The MID function will begin the extraction after counting to the right, one character at a time, based on the value you provide here. Note that if you enter a number that's greater than the target's total length, the result will be calculated as "empty text" (i.e. ""). If you enter a zero or negative number, you'll get an error.The last parameter ("4" in the example above): This is the length of the extraction, meaning how many characters you want. Like the offset, they're counted one character at a time, to the right, and starting with the character after the offset.

The MID Function in Action

Consider the following example. Here, we're targeting the text in A1 ("Lifewire is the most informative site on the Interwebs.") with the formula in A2: =MID(A1,17,4) The second parameter indicates a 17-character offset. As shown below with the red numbers, this means the extraction will start from the "m" in the word "most." Then, a length of 4 means four characters (including the first one) will be extracted, which should return the word "most" as the result (illustrated with the blue numbers). Entering this formula in the cell A2, we can see this is correct.

MIDB the More Complicated Sibling

MID has a related function, MIDB, which uses the same syntax. The only difference is that it works in bytes, not characters. A byte is 8 bits of data, and most languages can represent all their characters using a single byte. So why is MIDB useful? If you have the need to process double-byte characters, you may have cause to use MIDB. The so-called "CJK" languages (Chinese, Japanese, and Korean) represent their characters with two bytes instead of one, because there are so many of them. You can use the standard MID function on these, and it will select one whole character at a time, which is probably what you want anyway. But if you're doing some advanced processing on them and need to get the bytes that make them up, this function may help you out. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire Use the Excel RIGHT Function to Extract Characters How to Use the Excel DATEVALUE Function How to Use the Excel DATE Function How to Use the DAY function in Excel How to Use the COUNTIF Function in Excel How to Use the Google Spreadsheets AVERAGE Function How to Use the Round Function in Excel How to Use the ISBLANK Function in Excel How to Combine the ROUND and SUM Functions in Excel How to Use the Excel INDEX Function How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT How to Use the MONTH Formula in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use Excel's MROUND Function How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Excel TRUNC Function Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies