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
- MID returns a specific number of characters from a text string, starting at start_num and continuing through start_num + num_chars.
- Use the MID function when you want to extract text from inside a text string, based on location and length.
- You can use FIND or SEARCH to locate start_num when you don't know the location in advance.
MID formula examples
To get the third character from a string in A1, you can use the MID function like this:
=MID(A1,3,1)
The first argument is a cell reference, the second argument specifies the start number, and the third argument...
This is an experimental formula to strip characters from text. The experimental part is using character codes instead of regular characters as a way to make the formula case-sensitive, and providing a way to reverse the...
Note: in this example, we are calculating the end position in order to extract a substring with a literal start and end position. However, if you know the number of characters to extract, you can just plug in that...
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
CELL returns this result to the MID function as the text...
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...
At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with...
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...
This formula works for times entered in a particular format as shown below:
00h01m13s
00h01m08s
08h02m59s
Note the text string is always 9 characters long, and each component is 2 digits.
The core of this formula...
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 named range "sheetnames" is created with this code:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK is a macro command that retrieves an array of sheet names in the current workbook. The resulting array looks like...
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as...
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...
Working from the inside out, the LEN function calculates the length of the string, and this is joined by concatenation to "1:", creating a text range like this: "1:3"
This text is passed into INDIRECT, which evaluates...
Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...
At the core, this formula looks for a line delimiter ("delim") and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.
Note: In older versions of Excel on a Mac, use CHAR(13) instead of...
Related functions
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
