Excel Function: MID

Purpose

The MID function returns a subset of the characters contained in a text value. Its output is always a text value. The value passed to the start_num argument defines the position of the leftmost character to be returned, and the num_chars argument defines the total number of characters to return.

Invocations of the MID function take the form:

MID(text,start_num,num_chars)

Example

Consider a cell containing the formula:

=MID("abcdefg",2,4)

The formula instructs the MID function to extract a sequence of 4 characters from the text value “abcdefg“. First, the character in position 2 (“b“) is located. Next, a text value is formed consisting of “b” and the 3 characters succeeding it (“cde“). Finally, the value “bcde” is returned. The process is illustrated below:

Step 1: locate the character in position 2 ("b") of "abcdefg". Step 2: isolate 4 sequential characters, whose leftmost character is "b". Step 3: return "bcde".

Arguments

Argument	Argument type	Description
text	Mandatory	A text value MID will extract a subset of characters from.
start_num	Mandatory	The position of the leftmost character to be extracted from the text argument.
num_chars	Mandatory	The number of characters to be extracted from the text argument.

Related Functions

Function	Example	Purpose

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	The value suppled to start_num was ≤ 0.
#VALUE!	Bad argument	The value supplied to num_chars was < 0.

Examples

Example	Formula	Result	Description
1	`=MID("abcdefg",2,3)`	`bcd`	Extract 3 characters from “abcdefg“, starting from the character at position 2. The character at position 2 is “b“. The sequence of 3 characters starting from “b“, is “bcd“.
2	`=MID("abcdefg",2,15)`	`bcdefg`	Extract 15 characters from “abcdefg“, starting from the character at position 2. The character at position 2 is “b“. 15 characters do not exist between “b” and the end of “abcdefg“. Fewer characters being available than were requested does not cause an error. All available characters are extracted and “bcdefg” is returned.
3	`=MID("abcdefg",10,1)`		Extract 1 character from “abcdefg“, starting from the character at position 10. No character exists at position 10. Requesting characters outside the bounds of “abcdefg” does not cause an error. The empty text value is returned.
4	`=MID("abcdefg",1,0)`		Extract 0 characters from “abcdefg“, starting from the character at position 1. The character at position 1 is “a“. 0 characters are extracted and the empty text value is returned.

Examples: Errors

Example	Formula	Result	Description
1	`=MID("abcdefg",0,2)`	`#VALUE!`	Extract 2 characters from “abcdefg“, starting from the character at position 0. Because no character can exist at position 0 in a text value, #VALUE! is returned.
2	`=MID("abcdefg",1,-1)`	`#VALUE!`	Extract -1 characters from “abcdefg“, starting from the character at position 1. A text value cannot be constituted from a negative number of characters. #VALUE! is returned.