Part 1: Introduce
Definition: The MIDB function in Microsoft Excel extracts a specific number of characters from a text string, considering double-byte character languages, starting at a specified position.
Purpose: The MIDB function is handy when working with languages that use double-byte characters, such as Chinese, Japanese, or Korean, where each character occupies two bytes of storage.
Syntax & Arguments:
MIDB(text, start_num, num_chars)text: The text string from which you want to extract characters.start_num: The starting position from where you want to remove characters.num_chars: The number of characters you want to extract from the text.
Return value: The MIDB function returns the specified number of characters from the text string, starting at the fixed position, considering double-byte character languages.
Remarks:
- If the
start_numis less than 1, the function returns an empty string (“”). - If the
start_numis greater than the length of thetext, the function returns an empty string (“”). - If the
num_charsis negative, the function returns an empty string (“”). - If the
num_charsexceeds the number of characters available from thestart_numposition to the end of the text, the function returns all the remaining characters.
- If the
Part 2: Examples
Example 1: Extracting First Name from Full Name Suppose you have a table with full names in Column A and want to extract only the first name from each full name. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Full Name | First Name |
| 2 | John Smith | =MIDB(A2, 1, FIND(” “, A2)-1) |
| 3 | Jane Doe | |
| 4 | Tom Johnson |
- In cell B2, use the formula
=MIDB(A2, 1, FIND(" ", A2)-1)to extract the first name from the full name. - The FIND function is nested within the MIDB function to determine the position of the space character (” “) in the full name, and the MIDB function then extracts the characters from the beginning until one character before the space.
- The result in cell B2 will be “John” for the full name “John Smith”.
Example 2: Extracting the Last Word from Sentence Suppose you have a list of sentences in Column A and want to extract the last word from each sentence. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Sentence | Last Word |
| 2 | I love using Microsoft Excel for data analysis. | =MIDB(A2, FIND(“@”, SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”)))) + 1, LEN(A2)) |
| 3 | This is a sample sentence for demonstration. | |
| 4 | Extracting the last word can be useful. |
- In cell B2, use the formula
=MIDB(A2, FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))) + 1, LEN(A2))to extract the last word from the sentence. - The SUBSTITUTE function replaces the last space with the “@” symbol, and then the FIND function finds the position of the “@” mark. The MIDB function extracts the characters from one place after the “@” symbol until the end of the text.
- The result in cell B2 will be “analysis” for the sentence “I love using Microsoft Excel for data analysis.”
Example 3: Extracting Nth Character from Text Suppose you have a list of words in Column A and want to extract the third character from each word. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Word | Nth Character |
| 2 | Excel | =MIDB(A2, 3, 1) |
| 3 | Data | |
| 4 | Analysis |
- In cell B2, use the formula
=MIDB(A2, 3, 1)to extract the third character from the word. - The MIDB function extracts a single character starting from the third position in the word.
- The result in cell B2 will be “c” for the word “Excel”.
Example 4: Extracting Company Code Suppose you have a list of product codes in Column A, and each code consists of a company code followed by a product number. You want to extract only the company code. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Product Code | Company Code |
| 2 | ABC-123 | =MIDB(A2, 1, FIND(“-“, A2)-1) |
| 3 | XYZ-456 | |
| 4 | PQR-789 |
- In cell B2, use the formula
=MIDB(A2, 1, FIND("-", A2)-1)to extract the company code from the product code. - The FIND function is nested within the MIDB function to determine the position of the hyphen (“-“) in the product code, and the MIDB function then extracts the characters from the beginning until one character before the hyphen.
- The result in cell B2 will be “ABC” for the product code “ABC-123”.
Example 5: Extracting Domain Name Suppose you have a list of email addresses in Column A and want to extract only the domain name from each email address. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Email Address | Domain Name |
| 2 | [email protected] | =MIDB(A2, FIND(“@”, A2)+1, LEN(A2)) |
| 3 | [email protected] | |
| 4 | [email protected] |
- In cell B2, use the formula
=MIDB(A2, FIND("@", A2)+1, LEN(A2))to extract the domain name from the email address. - The FIND function finds the position of the “@” symbol in the email address, and the MIDB function then extracts the characters from one place after the “@” symbol until the end of the text.
- The result in cell B2 will be “example.com” for the email address “[email protected]“.
Example 6: Extracting Product Category Suppose you have a list of product codes in Column A, and each code consists of a product category followed by a product number. You want to extract only the product category. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Product Code | Product Category |
| 2 | C00123 | =MIDB(A2, 2, 3) |
| 3 | E00234 | |
| 4 | S00345 |
- In cell B2, use the formula
=MIDB(A2, 2, 3)to extract the product category from the product code. - The MIDB function extracts three characters starting from the second position in the product code.
- The result in cell B2 will be “001” for the product code “C00123”.
Example 7: Extracting Month from Date Suppose you have a list of dates in Column A, and you want to extract the month from each date. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Date | Month |
| 2 | 01/15/2022 | =MIDB(TEXT(A2, “mm/dd/yyyy”), 1, 2) |
| 3 | 03/22/2022 | |
| 4 | 11/07/2022 |
- In cell B2, use the formula
=MIDB(TEXT(A2, "mm/dd/yyyy"), 1, 2)to extract the month from the date. - The TEXT function converts the date to a text string in the desired format (“mm/dd/yyyy”), and the MIDB function then extracts the first two characters, representing the month.
- The result in cell B2 will be “01” for the date “01/15/2022”.
Example 8: Extracting Employee ID Suppose you have a list of employee IDs in Column A, and each ID consists of a department code followed by a unique number. You want to extract only the unique number. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Employee ID | Unique Number |
| 2 | D00123 | =MIDB(A2, 2, LEN(A2)-1) |
| 3 | M00234 | |
| 4 | S00345 |
- In cell B2, use the formula
=MIDB(A2, 2, LEN(A2)-1)to extract the unique number from the employee ID. - The MIDB function extracts the characters from the second position in the employee ID until the end of the text.
- The result in cell B2 will be “00123” for the employee ID “D00123”.
Example 9: Extracting Text Between Delimiters Suppose you have a list of strings in Column A, and each string is enclosed between two special characters. You want to extract the text between those special characters. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | String | Extracted Text |
| 2 | [ABC123] | =MIDB(A2, 2, FIND(“]”, A2)-2) |
| 3 | {XYZ456} | |
| 4 | (PQR789) |
- In cell B2, use the formula
=MIDB(A2, 2, FIND("]", A2)-2)to extract the text between the square brackets. - The FIND function is nested within the MIDB function to determine the position of the closing bracket (“]”) in the string, and the MIDB function then extracts the characters starting from the second position until two characters before the closing bracket.
- The result in cell B2 will be “ABC123” for the string “[ABC123]”.
Example 10: Extracting Substring Using Variable Length Suppose you have a list of strings in Column A and want to extract a substring from each line starting from a variable position and with a variable length. Here’s how you can do it:
| A | B | C | |
|---|---|---|---|
| 1 | String | Start | Length |
| 2 | Hello World | 7 | 5 |
| 3 | Good Morning | 3 | 7 |
| 4 | Excel Power | 6 | 3 |
- In cell C2, use the formula
=MIDB(A2, B2, C2)to extract a substring from the string based on the start position and length specified in columns B and C, respectively. - The MIDB function extracts the specified number of characters from the start position in the string.
- The result in cell C2 will be “World” for the string “Hello World” with a start position of 7 and length of 5.
Part 3: Tips and Tricks
- The MIDB function is handy when working with languages that use double-byte characters, where each character occupies two bytes of storage.
- Ensure that the
start_numandnum_charsarguments are specified correctly to extract the desired portion of the text. - To remove a range of characters, you can modify the
start_numandnum_charsarguments accordingly. - Combine the MIDB function with FIND, SUBSTITUTE, or LEN to perform more complex text manipulations.