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 words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user...
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 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...
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 remove the protocol (i.e. http://, ftp://, etc.) and trailing slash from a URL, you can use a formual based on the MID, FIND, and LEN functions. In the example shown, the formula in C5 is:
=MID(B5,FIND("//...
To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or...
To extract text between parentheses, braces, brackets, etc. you can use a formula based on the MID function, with help from SEARCH function. In the example shown, the formula in C5 is:
=MID(B5,SEARCH("(",B5...
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 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 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 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 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...
You can reverse a text string with the TEXTJOIN and MID functions, by using an array constant. In the example shown, the formula in C5 is:
=TEXTJOIN("",1,MID(B5,{10,9,8,7,6,5,4,3,2,1},1))
How this formula...
To capitalize the first letter in a word or string, you can use a formula based on the LEFT, MID, and LEN functions. In the example shown, the formula in C5 is:
=UPPER(LEFT(B5))&MID(B5,2,LEN(B5))
How this formula...
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...
Related functions
Good links
