How to Use the MID, MIDBs Function in Excel
Welcome to this article, where we’ll explore the use of the MID and MIDBs functions in Microsoft Excel and Google Sheets. These functions allow you to extract a specified number of characters from a text string, starting at a particular position.
Basic Syntax
The syntax for the MID function is as follows:
=MID(text, start_num, num_chars)text: The text string from which characters are to be extracted.start_num: The position where the extraction of characters starts.num_chars: The number of characters to be extracted.
The MIDBs function, used in Google Sheets particularly for double-byte character languages like Japanese, Chinese, or Korean, functions similarly.
Example 1: Using MID
Consider that cell A1 contains the phrase “Hello, World!”. To extract the word “World”, use the following formula:
| Formula | Result |
|---|---|
| =MID(A1, 8, 5) | World |
Example 2: Using MIDBs
If you have a cell in Google Sheets (A1) with the Chinese characters “你好，世界！”, and you wish to extract the second character, you would use:
| Formula | Result |
|---|---|
| =MIDBs(A1, 2, 1) | 好 |
Scenario: Extracting Domain Name
Imagine you need to extract domain names from a column of email addresses. This can be accomplished by combining the MID function with FIND and LEN. For an email address in cell A1, use:
=MID(A1, FIND("@", A1) + 1, LEN(A1) - FIND("@", A1))This formula locates the “@” symbol, begins extracting characters right after it, and computes how many characters remain to be extracted up to the end of the string.
By using the MID and MIDBs functions, you can efficiently pull out specified portions of data from text strings in both Excel and Google Sheets. Make sure to adapt the cell references and ranges as per your data structure.
More information: https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028