Syntax
MID(text,start_num,num_chars)
MIDB(text,start_num,num_bytes)
Definition. The MID() function returns characters from the middle of a text string. The length of the character string is indicated by num_chars, and the starting position for the search is start_num. Use MIDB() for double-byte characters.
Arguments
text (required). The string from which the characters are to be extracted
start_num (required). The starting position for the character string to be extracted
num_chars/num_bytes (required). Specifies how many characters/bytes to return from text
Background. Use the MID() function to return characters from the middle of a string. You can enter letters or numbers in the text argument. The MID(), LEFT(), and RIGHT() functions are especially useful if strings have a standard format, as with ZIP Codes, locations, or ISBNs.
Note the following when working with this function:
MID() returns an empty string (“”) if start_num is greater than the length of the string.
MID() returns all characters to the end of the text if start_num is smaller than the length of the string but start_num plus num_chars exceeds the length of the string.
MID() returns the
#VALUE!error if start_num is less than or equal to 1.MID() returns the
#VALUE!error if start_num is negative.
Example. You might need to extract the product group from a column containing item numbers made up of 10 digits, where:
The main product group is the first two digits.
The product group is the next three digits.
The product number is the remaining five digits.
So for an item number of 2301511290, you can use the MID() function to locate the product group, which starts at position 3 and is three digits long (see Figure 8-7). The formula
=MID("2301511290",3,3)returns 015 as the product group.
Further examples:
=MID("intercontinentalflight",1,5)returnsinter.=MID("gazelle",1,4)returnsgaze.=MID("gazelle",4,4)returnselle.=MID("Louis",2,3)returnsoui.=MID("Excel",1,2)returnsEx.=MID("Excel",2,3)returnsxce.