Excel MID Function

=MID (text, start_num, num_chars)

The MID function extracts a given number of characters from the middle of a supplied text string. MID takes three arguments, all of which are required. The first argument, text, is the text string to start with. The second argument, start_num, is the position of the first character to extract. The third argument, num_chars, is the number of characters to extract. If num_chars is greater than the number of characters available, MID returns all remaining characters.

Examples

The formula below returns 3 characters starting at the 5th character:

 
=MID ("The cat in the hat",5,3) // returns "cat"

This formula will extract 3 characters starting at character 16: 
 
=MID ("The cat in the hat",16,3) // returns "hat"

If num_chars is greater than remaining characters, MID will all remaining characters: 
 
=MID ("apple",3,100) // returns "ple"

MID can extract text from numbers, but the result is text:
[external_link offset=1] 

 
=MID (12348,3,4) // returns "348" as text

Related functions
Use the MID function to extract from the middle of text. Use the LEFT function to extract text from the left side of a text string and the RIGHT function to extract text starting from the right side of text. The LEN function returns the length of text as a count of characters. Use FIND or SEARCH to locate an unknown start position. 
Notes

num_chars is optional and defaults to 1.
MID will extract text from numeric values, but the result is text
Number formatting is not counted or extracted.

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…

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…

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…

The DATE function creates a valid date using three arguments: year, month, and day: =DATE(year,month,day) In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from a text string…

The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so: =IF(ISNUMBER(B4) For any number less than the value in “input”, the formula will return TRUE and the…

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-…

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…

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…

In this example the goal is to parse feet and inches out in 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 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…

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…

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…

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…

The first expression uses LEFT and UPPER to capitalize the first letter: =UPPER(LEFT(B5)) No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters…

First, note that the cells in F5:F13 are formatted as Text prior to entry. This allows the times to contain leading zeros like “083000”. Alternately, you can enter these time strings with a single quote at the start…