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 split dimensions that like 100x50x25 into three separate parts, you can use some rather complicated formulas that use LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE.
Note: you can also use Flash Fill in Excel 2013 and...
To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. In the example shown, the formula in F8 is...
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...
When date information from other systems is pasted or imported to Excel, it may not be recognized as a proper date or time. Instead, Excel may interpret this information as a text or string value only.
To convert a...
To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms...
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.
Note: Before you go the...
To extract the last two words from a cell, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. In the example shown, the formula in C5 is:
=MID(B5,FIND("@",...
To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate...
To generate basic outline numbering you can use a formula based several Excel functions, including COUNTA, IF, MID, FIND, and LEN. In the example shown, the formula in D5 is:
=COUNTA($B$5:B5)&"."&IF(...
If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
How...
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 count numbers where the third digit equals 3, you can use a formula based on the SUMPRODUCT and MID functions. In the example shown, the formula in E5 is:
=SUMPRODUCT(--(MID(B5:B12,3,1)="3"))
How this...
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 remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. In the example shown, the formula in C5 is:
=TEXTJOIN("",TRUE,IF(...
To convert dates in text format dd/mm/yy to a true date in mm/dd/yy format, you can use uses a formula based on the DATE function. In the example shown, the formula in C5 is:
=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5...
Related functions
Good links
