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 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 remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function. In the example shown, the formula in C5 is:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(...
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 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...
To highlight numbers less than a certain value, including numbers entered as text like "
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 remove the last word from a text string, you can use a formula based on the MID function, with help from SUBSTITUTE, LEN, and FIND. In the example shown, the formula in cell B5 is:
=MID(B5,1,FIND("~",...
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 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,...
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...
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 abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, a new function available in Office 365 and Excel 2019. You can use this approach to create initials from...
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 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...
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...
Related functions
