The MID function in Google Sheets helps you extract a specific section of text from a string based on the starting position and the number of characters you want. It’s ideal for splitting structured text, such as product codes, serial numbers, or names.
How It Works
The MID function extracts characters from a text string starting at a specified position and for a defined length.
- Input the text: Provide the cell reference or text string to work with.
- Specify the start position: Determine where to begin extracting characters.
- Define the length: Indicate how many characters to extract from the start position.
MID Function Syntax in Google Sheets
=MID(text, start_num, num_chars)
Parameters:
- text: (Required) The text string or cell reference containing the text to extract from.
- start_num: (Required) The position (number) of the first character to extract.
- num_chars: (Required) The number of characters to extract from the starting position.
1. How to use the MID Function in Google Sheets
The MID function in Google Sheets allows you to extract a specific portion of text from a string by defining the starting point and the number of characters you want to extract.
Step 1: Set Up the Data and Select a Cell
Create a table with text data. For example, let’s say you have product codes and you want to extract specific parts of them. Select a Cell for the result of formula.
Step 2: Write the MID Formula
In a new column (e.g., Column C), use the MID function to extract part of the Product Code. Suppose you want to extract 3 characters starting from position 4 in the Product Code. Enter the formula in cell C2:
=MID(B2, 4, 3)
Explanation:
- B2: The cell reference that contains the text (Product Code).
- 4: The starting position in the text (starting from the 4th character).
- 3: The number of characters to extract.
Step 3: Apply the Formula to Other Rows
Click the small square in the bottom-right corner of the cell (C2) and drag it down to fill the formula for the remaining rows.
Step 4: View the Results
The MID function will extract the specified characters from each Product Code. After applying the formula, your results will look like this:
2. Practical Use Cases for Text Extraction in Google Sheets
The MID function in Google Sheets is versatile for handling text. Here are some practical examples for MID function to extract and manage data efficiently using Google Sheets functions for text:
Example 1: Extracting Email Domains
Extract the domain (e.g., "gmail.com") from email addresses in cell A1.
Formula:
=MID(A1, FIND("@", A1) + 1, LEN(A1) - FIND("@", A1))
- Use Case: Organize email data for marketing or analysis.
- Example: From "user@gmail.com," it extracts "gmail.com."
Example 2: Splitting Product IDs
Split a product ID like "SKU-1234-AB" to isolate the numeric portion "1234".
Formula:
=MID(A1, 5, 4)
- Use Case: Extract meaningful components from structured product IDs.
- Example: From "SKU-1234-AB," it returns "1234."
Example 3: Extracting File Extensions
Extract file extensions such as ".png" from filenames in cell A1.
Formula:
=MID(A1, FIND(".", A1) + 1, LEN(A1) - FIND(".", A1))
- Use Case: Classify files based on type for better organization.
- Example: From "image.png," it extracts "png."
These practical examples show how to efficiently use MID and FIND functions for text extraction and data processing in Google Sheets.
3. Combining MID with Other Functions
The MID function becomes even more powerful when combined with other functions for advanced text manipulation. Here are some practical combinations:
1. MID + FIND
Extract dynamic substrings based on specific characters or patterns.
Formula:
=MID(A1, FIND("-", A1) + 1, FIND("-", A1, FIND("-", A1) + 1) - FIND("-", A1) - 1)
- Use Case: Extract text between two dashes in a string like "SKU-1234-AB".
- Example: From "SKU-1234-AB," it extracts "1234."
2. MID + CONCATENATE
Combine extracted substrings with additional text or characters.
Formula:
=CONCATENATE(MID(A1, 1, 3), "-", MID(A1, 4, 4))
- Use Case: Reformat IDs or add separators for better readability.
- Example: From "SKU1234," it creates "SKU-1234."
3. MID + LEN
Handle variable-length strings by adjusting the number of characters dynamically.
Formula:
=MID(A1, 3, LEN(A1)-2)
- Use Case: Remove the first two characters while keeping the rest of the text.
- Example: From "ABC12345," it returns "C12345."
These combinations allow the MID function to handle dynamic scenarios like text extraction, formatting, and data manipulation efficiently in Google Sheets.
Also Read: