How to use MID Function in Google Sheets

Handling textual data in spreadsheets often involves tasks like splitting information, extracting specific elements, or organizing text for analysis. The MID function in Google Sheets simplifies these processes by allowing users to extract specific parts of a text string. Whether you’re working with email addresses, product IDs, or file names, the MID function offers a versatile solution for precise text manipulation and data organization. This guide provides practical examples and step-by-step instructions for using the MID function, along with advanced combinations for more complex scenarios.

Google Sheets MID Function

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.

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:

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 "[email protected]," 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.

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:

• Google Sheets MODE Function
• Google Sheets MIN Function
• Google Sheets MEDIAN Function

Conclusion

The MID function in Google Sheets is a powerful tool for tackling a wide range of text-related challenges, from extracting email domains to segmenting product IDs. By leveraging its flexibility and combining it with other functions like FIND, CONCATENATE, and LEN, users can handle complex tasks with greater efficiency. Experimenting with these techniques will not only enhance your data manipulation skills but also streamline your workflow in Google Sheets.