How to Use the Excel MID Function

This function helps you extract text from a cell

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 target text in cell A1, and the MID formula's results in cell A2.

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.

Demonstrating the MID function's offset and length parameters in a text string.

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?