MID

• How to use MID formula?
• Examples of using MID formula
• MID formula not working?
• Similar formulas to MID

Usage

Use the MID formula with the syntax shown below, it has 3 required parameters:

=MID(string, starting_at, extract_length)

1. string (required):
   The string of text that you want to extract characters from.
2. starting_at (required):
   The starting point in the string where you want to begin extracting characters. This must be a positive number or zero.
3. extract_length (required):
   The number of characters that you want to extract from the string. This must be a positive number or zero.

Examples

Extracting First Name from Full Names

If you have a list of full names in a column in Google Sheets, you can use the MID formula to extract the first name from each name. This is useful when you want to address someone by their first name in an email or letter.

Extracting Postal Codes from Addresses

If you have a list of addresses in a column in Google Sheets, you can use the MID formula to extract the postal code from each address. This is useful when you want to sort your data by postal code or analyze data based on location.

Extracting Numbers from Text

If you have a column of text that contains numbers in the middle of the text, you can use the MID formula to extract the numbers. This is useful when you want to perform calculations on the numbers or sort the data based on the numeric values.

Common Mistakes

Counting from zero instead of one

The MID function counts the position of characters from 1 for the left-most character. If you count from 0, you may extract the wrong substring.

Extracting more characters than the input string has

If you specify an extract length that is greater than the number of characters in the input string, the function will return all the available characters from the starting position to the end of the string.

Specifying a negative starting position

The MID function does not support negative starting positions. If you specify a negative number, the function will return an error.

Related Formulas

The following functions are similar to MID or are often used with it in a formula:

• LEFT

  The LEFT formula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.

• RIGHT

  The RIGHT formula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.

• LEN

  The LEN formula in Google Sheets is used to count the number of characters in a given text string. It is commonly used to check the length of text inputs, or to find the length of a cell value or range.

• FIND

  The FIND formula in Google Sheets searches for a specific text within another text and returns the position of the first occurrence of the text. This formula is often used to extract a substring from a larger string or to check if a certain text exists within another text.

• SUBSTITUTE

  The SUBSTITUTE formula is used to replace specific text in a string with new text. It is commonly used to replace all occurrences of a certain word or character within a larger string. The formula has the option to specify which occurrence to replace, or it will replace all occurrences by default.

Learn More

You can learn more about the MID Google Sheets function on Google Support.