The MID function extracts a string of characters from a string, given a starting position and a number of characters to extract.
MID(
text,
start_num,
num_chars
)
- text: Text from which you want to extract the characters, or name of the column that contains the texts from which to extract the characters.
- start_num: Position of the first character to extract. The position of the first character from the left is 1.
- num_chars: Number of characters to extract.
The MID function returns a text string.
start_num must be an integer greater than zero (otherwise the function returns an error). If start_num is greater than the length of the text to extract the string from, MID returns an empty string. Thus, in the following example, a character is being extracted from position 1 and in the fourth record the string from which to extract the characters is an empty string: The extracted string is also an empty string.
If there are not enough characters from start_num to the end of the string to extract a string of length num_chars, the function simply returns the characters up to the end of the string. In the following example we want to extract a 5-character string from position 5 and none of the initial text strings are long enough:
num_chars can be 0 (in which case an empty text string will be extracted) but it can never be a negative value.
The following example uses the SEARCH function to find the first occurrence of the letter "a" in the "Manufacturer" column that contains manufacturer names, and the MID function to return three characters from that position. The MID function is included as the first argument of the IFERROR function so that, in those cases in which no letter "a" is found (occasions when the SEARCH function is going to return an error), a controlled result is returned (a Blank in this case).
Note that, in the last row, the letter "a" occupies the last position of the text string, so MID only returns that letter (which can be confirmed by looking at the length of the extracted text).
In this other example we want to extract from the product name its initial and the characters that follow the blank space, regardless of the length of the text that precedes or follows this space. To do this, the position of the blank space is calculated and the string is identified from the second character to that position, replacing it with an empty text string. Finally all the returned text is converted to lowercase:
