Excel MID Function

What is the Excel MID Function?

The excel MID function extracts a certain number of texts from the specified position.

The MID function acts like the LEFT function if it takes characters from the first position or works like the RIGHT function if the number of characters taken reaches the end of the text.

MID Syntax

MID(text, start_num, num_chars)

text, required, the text containing the characters to extract from.

start_num, required, the first character position to extract.

num_char, required, the number of characters to extract.

How to Use MID Function in Excel

The following is an example of the MID function usage and the results.

MID Function #1

=MID(A2,12,5)

You got nothing it start_num argument greater then text length.

MID Function #2

=MID(A3,8,1000)

If num_char argument exceeds the text length, whatever the number you get the characters up to the end of the text.

MID Function #3

=MID(A4,0,6)

#VALUE! error appears if the start_num argument is smaller than 1, it could be 0 (zero) or a negative number.

MID Function #4

=MID(A5,1,6)

The MID function returns the same value as the LEFT function if start_num argument is equal to 1.

The MID Function #4 result is the same as the LEFT(A5,6) formula.

MID Function #5

=MID(A6,8,4)

The results of the MID function and the RIGHT function are the same if the num_char argument plus start_num argument is equal to the text length.

The MID function #5 result is the same as the RIGHT(A6,4) formula.

MID Function #6

=MID(A7,2,2)

The MID function can extract from a number too.

MID Function #7

=MID(A8,2,2)

Alphanumeric is not a problem for the MID function, because alphanumeric is a text.

MID Function #8

=MID(A9,2,2)

The MID function returns the same results for numbers 123456 and “Wednesday, January 3, 2238”. Why? Because Excel stores date in an integer number. “Wednesday, January 3, 2238” is the result of “Format cells” menu. The real value is an integer number 123456.

The MID function extracts the number but ignores the cell format.

MID Function #9

=MID(A10,2,2)

Why are the results of MID Function #9 and #10 not the same? Because cell A9 contains a date and cell A10 contains a text.

“Wednesday, January 3, 2238” in cell A9 is the result of the format cell menu while in cell A10 is the real value.

MID Function #10

=MID(A11,2,4)

Just like integer numbers, the MID function can extract decimal numbers and assuming decimal separators as one character.

MID Function #11

=MID(A12,2,4)

The results are like MID Function #10. Excel stores date value in integer number, while for time value excel stores as a decimal number.

If you format 2:57:46 AM to number-general the result is 0.12345.

MID Example

Another Text Function