Excel MID Function

MID Function

The MID function is a premade function in Excel, which returns characters from the middle of a text.

You choose where to start, and how many characters to return. It works like LEFT and RIGHT, but it can start at any position in the text.

It is typed =MID and has the following parts:

Text: The text to take characters from. This is usually a cell.

Start_num: The position of the first character to return. The first character in the text is position 1.

Num_chars: How many characters to return.

The item code PKB-0200-BALL has 13 characters. This is the position of each one:

=MID(B2, 5, 4) starts at position 5 and returns 4 characters. If B2 holds PKB-0200-BALL, the result is 0200.

How to use the MID function:

1. Select a cell (C2)
2. Type =MID
3. Double click the MID command
4. Select the cell with the text (B2)
5. Type (,)
6. Type the position to start at (5)
7. Type (,)
8. Type the number of characters to return (4)
9. Hit enter

Let's have a look at an example!

These are the products in the Poke Mart shop, with their item codes. Copy them and paste them into cell A1 to follow along:

Each item code has three parts, separated by dashes: a short name for the item, the price with four digits, and the category.

Type the label Price in C1.

Use the MID function to get the price from the middle of each code:

MID starts at character 5 in B2 and returns 4 characters. For PKB-0200-BALL, that is 0200.

The function is repeated with the filling function for each row, down to C11.

MID Returns Text

Look at column C again. The prices are aligned to the left, and 0200 keeps its zero at the start.

That is because MID always returns text, also when the characters are digits. Excel does not calculate with text. If you add up C2:C11 with SUM, the result is 0.

Put MID inside the VALUE function to change the text into a number. Type the label Price (number) in D1, and the formula in D2. Fill it down to D11.

Then type the label Sum in A12, =SUM(C2:C11) in C12 and =SUM(D2:D11) in D12:

The formula in D2:

The formula in D12:

The prices in column D are numbers. They are aligned to the right, and SUM returns 6250. The same SUM on the text in column C returns 0.

At the End of the Text

If num_chars asks for more characters than there are left, MID returns the characters up to the end of the text. If start_num is after the last character, MID returns an empty text.

Type PKB-0200-BALL in A2, A3 and A4 of a new sheet:

The formulas in B2, B3 and B4:

B2 asks for exactly the last 4 characters, BALL.

B3 asks for 100 characters. There are only 4 left from position 10, so MID returns BALL without an error.

B4 starts at position 20, but the text has only 13 characters. The result is an empty text, so the cell looks empty.

If start_num is less than 1, or num_chars is less than 0, MID returns the #VALUE! error.

Going Further: MID With FIND

In the codes above, the price always starts at position 5. In some lists, the first part of a code has a different length in each row, so the start position changes too.

Then use the FIND function to find the position of the dash. FIND returns the position of a text inside another text, counted from the left:

These codes start with the name of the item, so the first part has a different length in each row:

The formula in B2 finds the position of the first dash:

The formula in C2 starts one character after the dash, and returns 4 characters:

For POKE-0200-BALL, the dash is character 5, so MID starts at 6. For ANTIDOTE-0100-STAT, the dash is character 9, so MID starts at 10. The price is found in every row.

You do not need the helper column B. Put FIND inside MID to do it all in one cell:

Related Functions

MID is often used together with LEFT, RIGHT and LEN. To change characters instead of taking them out, use SUBSTITUTE.