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.
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 extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
In the example shown, the formula in D5 is:
=TRIM(MID(...
To convert text in an unrecognized date format to a proper Excel date, you can parse the text and assemble a proper date with a formula based on several functions: DATE, LEFT, MID, and RIGHT. In the example shown, the...
Top convert a timestamp entered as text into a proper Excel time, you can use the MID function to extract components and the TIME function to assemble the time. In the example shown, the formula in F5 is:
=TIME(MID(B5...
To get the name of the current worksheet (i.e. current tab) you can use a formula based on the CELL function. CELL retrieves the workbook name and sheet, and the MID and FIND functions are used to extract just the...
To parse a text string that represents a time into a proper Excel time, you can use a formula based on the RIGHT, LEFT, MID, and TIME functions. In the example shown, the formula in F5 is:
=TIME(LEFT(E5,2),MID(E5,3,2...
Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt.
Sometimes you encounter data that mixes...
To match text longer than 255 characters with the MATCH function, you can use the LEFT, MID, and EXACT functions to parse and compare text, as explained below. In the example shown, the formula in G5 is:
=MATCH(1,...
To strip html or other markup from values in cells, you can use the MID function.
In the example shown, the formula in C5 is:
=MID(B5,4,LEN(B5)-7)
How this formula works
The MID function returns characters using a...
To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. In the example shown, data validation is applied with this...
To convert a measurement in feet and inches to inches only (i.e. 4'5" to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. In the example shown, the formula in D5 is:
=...
To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. In the example shown, the formula...
To highlight numbers less than a certain value, including numbers entered as text like "
To split text at an arbitrary delimiter (comma, space, pipe, etc.) you can use a formula based on the TRIM, MID, SUBSTITUTE, REPT, and LEN functions. In the example shown, the formula in C5 is:
=TRIM(MID(SUBSTITUTE($...
To extract a substring with an Excel formula, you can use the MID function. In the example shown, the formula in E5 is:
=MID(B5,C5,D5-C5+1)
which, on row 5, returns "Perfect".
How this formula works
Note: in this...
To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called "sheetnames" with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range....
Related functions
