Description
Use this function to extract a specific number of characters from the middle of a text string, starting at a specified position. Supported in Chains.
MID is useful for extracting substrings, parsing data, and manipulating text in various ways.
Syntax
MID(text, start_num, num_chars)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
text |
Yes | The text string containing the characters you want to extract. | A text string, or a reference to a cell containing text. |
start_num |
Yes | The position of the first character you want to extract in text. | A positive integer, a cell reference that contains a positive integer, or a formula which results in a positive integer. |
num_chars |
Yes | The number of characters you want MID to return from text. | A positive integer, a cell reference that contains a positive integer, or a formula which results in a positive integer. |
Example
Sample Data
| A | B |
|---|---|
| 1 | Eggplant goulash |
| 2 | 123-45-6789 |
| 3 | ABCDEFGHIJKLMNOP |
| 4 | Mid Example |
Sample Formulas
| Formula | Description | Result |
|---|---|---|
=MID(B1,4,5) |
Extracts 5 characters from B1, starting at position 4. | plant |
=MID(B2,5,2) |
Extracts 2 characters from B2, starting at position 5. | 45 |
=MID(B3,2,5) |
Extracts 5 characters from B3, starting at position 2. | BCDEF |
=MID(B4,1,3) |
Extracts 3 characters from B4, starting at position 1. | Mid |
=MID(B1,LEN(B1)-3,4) |
Extracts the last 4 characters from B1. | lash |
Notes
- If
start_numis greater than the length of text, MID returns an empty string. - If
start_numis less than 1, MID treats it as 1. - If
num_charsis negative, MID returns a #VALUE! error. - If
num_charsis greater than the number of characters from start_num to the end of text, MID returns all characters from start_num to the end of text. - MID counts each character, including spaces and punctuation.
Tips
- Combine MID with FIND or SEARCH to extract text based on the position of specific characters.
- Combine MID with LEN to extract a specific number of characters from the end of a string.
- MID can be nested inside other text functions for more complex text manipulation.
- MID is useful for extracting specific fields when working with fixed-width data,
- MID is case-sensitive, it treats uppercase and lowercase letters as different characters.
Related functions
CHAR
CODE
CONCATENATE
CONCATENATEIF
FIND
LEFT
LEN
LOWER
MID
PROPER
REPT
RIGHT
SEARCH
SUBSTITUTE
TEXT
TEXTJOIN
TRIM
UNICHAR
UNICODE
UPPER