Excel MID Function
Summary
The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".
Purpose
Extract text from inside a string
Return value
The characters extracted.
Syntax
=MID (text, start_num, num_chars)
Arguments
- text - The text to extract from.
- start_num - The location of the first character to extract.
- num_chars - The number of characters to extract.
Version
Usage notes
The MID function extracts a given number of characters from the middle of a supplied text string. The first argument, start_num is position of the first character to extract. The second argument, num_chars, is the number of characters to extract. If num_chars is greater than the number of characters available, MID returns as many characters as possible.
The MID function is commonly combined with other functions like LEN and FIND to extract text in more complex scenarios. Also, the LEFT function will extract text starting from the left, and the RIGHT function will extract text from the right.
Examples
In the example below, we extract "cat" from "The cat in the hat":
=MID("The cat in the hat",5,3) // returns "cat"
This formula will extract hat:
=MID("The cat in the hat",16,3) // returns "hat"
Notes
- num_chars is optional and defaults to 1.
- RIGHT will extract digits from numbers as well as text.
- Number formatting is not counted or extracted.
- Use FIND or SEARCH to locate an unknown start position.
MID formula examples
Assuming you have a full name in column B, a first name in column C, and a last name in column D, you can use a formula that looks like this:
=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))
At the core, the MID...
At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters...
The cell function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheetname
At the highest level, the MID function simply extracts the file...
LAMBDA function can be used to create custom, reusable functions in Excel. This example illustrates a feature called recursion, in which a function calls itself. Recursion can be used to create elegant, compact, non-...
This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins.
The formula is a bit convoluted, but the steps are simple. We first count...
Working from the inside out, the MID function is used to cast the string into an array of individual letters:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
In this part of the formula, MID, ROW, INDIRECT, and...
At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a...
The foundation of this formula is the MID function, which extracts a specific number of characters from text, starting at a specific location. To figure out where to start extracting text, we use this expression:...
The core of this formula is the MID function, which extracts the text in a URL starting with the character after "//", and ending with the character before the trailing slash ("/"):
=MID(url,start,chars)
The url...
The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error. To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT...
At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings...
A MAC (Media Access Control) address is a unique identifier assigned to most network adapters. Two common IEEE 802 standards display a MAC address in 6 groups of 2 hexadecimal digits separated by a colon (:) or hyphen...
Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won't be able to perform math operations on such...
In this example the goal is to parse feet and inches out the text strings shown in column B, and create a single numeric value for total inches. The challenge is that each of the two numbers is embedded in text. The...
The 1st dimension
To get the first dimension, we are using this formula in C4:
=LEFT(B4,FIND("x",B4)-1)
This works by extracting text starting at the LEFT. The number of characters is calculated by...
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
