Excel MID Function allows you to extract a specific portion of text from a larger text string. It’s like cutting out a piece of a sentence or word from a longer paragraph. This can be really useful when you need to work with text data, such as names, dates, or other information.
Syntax
MID(text, start_num, num_chars)
Arguments
| text | The text from which you want to extract a portion. |
| start_num | The position in the text where you want to start extracting. |
| num_chars | The number of characters you want to extract from the text. |
How to Use
The MID function is straightforward to use. You just need to provide the function with the three arguments mentioned above.
Let’s look at a few examples to see how it works:
Example 1: Suppose you have the text “Excel is awesome” in cell A1, and you want to extract the word “awesome.” You can use the MID function like this:
|
1 |
=MID(A1, 9, 7) |
This formula starts at the 9th character (the space before “awesome”) and extracts 7 characters, giving you “awesome.”
Example 2: If you have a date in cell B1 like “01/15/2023” and you want to extract just the year “2023,” you can use the MID function this way:
|
1 |
=MID(B1, 7, 4) |
This formula starts at the 7th character (the first digit of the year) and extracts 4 characters, giving you “2023.”
Example 3 (Optional): You can also use the MID function to extract a variable-length portion of text. For instance, if you have the text “Hello, World!” in cell C1 and you want to extract everything after the comma and space, you can use the MID function with the FIND function like this:
|
1 |
=MID(C1, FIND(", ", C1) + 2, LEN(C1) - FIND(", ", C1) - 1) |
This formula finds the position of the comma and space using FIND, adds 2 to start after them, and calculates the number of characters to extract based on the length of the text and the position of the comma and space. It extracts “World!” in this case.
These examples demonstrate how the MID function can be a handy tool for extracting specific information from text in Excel.