MIDThe MID formula in Google Sheets is used to extract a specific number of characters from a string, starting at a specified point. This is useful when working with large datasets and you only need a portion of the information in a cell. The formula takes in the string to extract from, the starting point, and the length of characters to extract as arguments.
- How to use
- Examples of using
MIDformula not working?
- Similar formulas to
MID formula with the syntax shown below, it has 3 required parameters:
- string (required):
The string of text that you want to extract characters from.
- starting_at (required):
The starting point in the string where you want to begin extracting characters. This must be a positive number or zero.
- extract_length (required):
The number of characters that you want to extract from the string. This must be a positive number or zero.
ExamplesHere are a few example use cases that explain how to use the
MID formula in Google Sheets.
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.
MID not working? Here are some common mistakes people make when using the
MID Google Sheets Formula:
Counting from zero instead of one
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
MID function does not support negative starting positions. If you specify a negative number, the function will return an error.
The following functions are similar to
MID or are often used with it in a formula:
LEFTformula 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.
RIGHTformula 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.
LENformula 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.
FINDformula 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.
SUBSTITUTEformula 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.
You can learn more about the
MID Google Sheets function on Google Support.