How to use the MID function
The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.
Table of Contents
- MID Function Syntax
- MID Function arguments
- MID function example
- MID function from right
- MID function with FIND function
- MID function with search
- MID function between two characters
- MID function - split characters into an array
- MID function - reverse characters in value
- Excel text functions
- Get Excel *.xlsx file
1. Excel Function Syntax
MID(text, start_num, num_chars)
2. Arguments
text | Text string or a cell reference to a text string. |
start_num | The starting point. |
num_chars | The number of characters you want to extract. |
3. MID function example
The first argument is the text string, the second argument is the start position. In this case character number 3, note spaces are counted.
The third argument is how many characters you want to extract counted from the start position.
4. MID function from right
The formula shown in cell C3 in the image above extracts the last word from the right based on the last space character.
Formula in cell C3:
4.1 Explaining formula
Step 1 - Remove space charcaters
The TRIM function removes leading and trailing space characters.
TRIM(B3)
becomes
TRIM("A blue whale")
and returns "A blue whale".
Step 2 - Replace space character
The SUBSTITUTE function allows you to replace a given string of characters.
SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(TRIM(B3), " ", "")
becomes
SUBSTITUTE("A blue whale", " ", "")
and returns "Abluewhale".
Step 3 - Count characters
The LEN function returns a number representing the number of characters in a given string.
LEN(SUBSTITUTE(TRIM(B3), " ", ""))
becomes
LEN("Abluewhale")
and returns 10.
Step 4 - Subtract total character length with string without space characters.
The minus character lets you subtract numbers in an Excel formula.
LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", ""))
becomes
12-10 equals 2. There are two space characters in the string.
Step 5 - Replace last space with | character
SUBSTITUTE(B3, " ", "|", LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", "")))
becomes
SUBSTITUTE(B3, " ", "|", 2)
becomes
SUBSTITUTE("A blue whale", " ", "|", 2)
and returns "A blue|whale".
Step 6 - Find | character in string
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])
SEARCH("|", SUBSTITUTE(B3, " ", "|", LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", ""))))
becomes
SEARCH("|", "A blue|whale")
and returns 7. Character | is in the seventh positiion in the string.
Step 7 - Extract last word
MID(TRIM(B3), SEARCH("|", SUBSTITUTE(B3, " ", "|", LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", "")))), LEN(B3))
becomes
MID(TRIM(B3), 7, LEN(B3))
becomes
MID(TRIM(B3), 7, 12)
becomes
MID("A blue whale", 7, 12)
and returns "whale".
5. MID function with find
The formula in cell C3 uses the FIND function to search for a string also considering upper and lower letters as well.
Formula in cell C3:
5.1 Explaining formula
Step 1 - Find position of a given string
The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.
FIND(find_text,within_text, [start_num])
FIND("a", B3)
becomes
FIND("a", "ABA aFC AGH")
and returns 5.
Step 2 - Extract three characters based on position of a given string
MID(B3, FIND("a", B3), 3)
becomes
MID(B3, 5, 3)
becomes
MID("ABA aFC AGH", 5, 3)
and returns "aFC".
6. MID function with search
The formula in cell C3 extracts a substring based on a character position. The position is calculated using the SEARCH function.
Formula in cell C3:
6.1 Explaining formula
Step 1 - Find space charcater position in string
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])
SEARCH(" ", B3)
becomes
SEARCH(" ", "Red apple")
and returns 4.
Step 2 - Extract substring based on position
MID(B3, SEARCH(" ", B3), 6)
becomes
MID(B3, 4, 6)
becomes
MID("Red apple", 4, 6)
and returns "apple".
7. MID function between two characters
The formula in cell C3 extracts the characters between two given characters, in this example, the hastag character.
Formula in cell C3:
7.1 Explaining formula
Step 1 - Find character in string
The SEARCH function returns a number representing the position of the character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])
SEARCH("#", B3)+1
becomes
4+1 equals 5.
Step 2 - Count characters in cell
The LEN function returns a number representing the number of characters in a given string.
LEN(value)
LEN(B3)
becomes
LEN("ABA#132#CFG")
and returns 11.
Step 3 - Extract string
MID(B3, SEARCH("#", B3)+1, LEN(B3))
becomes
MID(B3, 5, LEN(B3))
becomes
MID(B3, 5, 11)
becomes
MID("ABA#132#CFG", 5, 11)
and returns "132#CFG".
Step 4 - Find second instance of character
SEARCH("#", MID(B3, SEARCH("#", B3)+1, LEN(B3)))-1
becomes
SEARCH("#", "132#CFG")-1
becomes
4-1 equals 3.
Step 5 - Extract string
LEFT(MID(B3, SEARCH("#", B3)+1, LEN(B3)), SEARCH("#", MID(B3, SEARCH("#", B3)+1, LEN(B3)))-1)
becomes
LEFT("132#CFG", SEARCH("#", MID(B3, SEARCH("#", B3)+1, LEN(B3)))-1)
becomes
LEFT("132#CFG", 3)
and returns "132".
8. MID function - split characters
This example demonstrates a formula that splits each character into a vertical array, there is also a formula below that splits characters horizontally.
Formula in cell D3:
8.1 Explaining formula
Step 1 - Count characters in cell B3
The LEN function returns a number representing the number of characters in a given string.
LEN(value)
LEN(B3)
becomes
LEN("strawberry")
and returns 10. There are ten characters in cell B3.
Step 2 - Create a list of numbers
The SEQUENCE function creates a list of sequential numbers.
SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(LEN(B3))
becomes
SEQUENCE(10)
and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}.
Step 3 - Split characters in value
MID(B3, SEQUENCE(LEN(B3)), 1)
becomes
MID("strawberry", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, 1)
and returns {"s"; "t"; "r"; "a"; "w"; "b"; "e"; "r"; "r"; "y"}.
Use this Excel 365 dynamic array formula to split characters horizontally:
Array formula for earlier Excel versions:
9. MID function - reverse characters in a value
Excel 365 dynamic array formula in cell D3:
Explaining formula
Step 1 - Count characters in cell B3
The LEN function returns a number representing the number of characters in a given string.
LEN(value)
LEN(B3)
becomes
LEN("strawberry")
and returns 10.
Step 2 - Count characters in cell B3
The SEQUENCE function creates a list of sequential numbers.
SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(LEN(B3), , LEN(B3), -1)
becomes
SEQUENCE(10, , 10, -1)
and returns {10; 9; 8; 7; 6; 5; 4; 3; 2; 1}.
Step 3 - Split characters in value
MID(B3, SEQUENCE(LEN(B3), , LEN(B3), -1), 1)
becomes
MID(B3, {10; 9; 8; 7; 6; 5; 4; 3; 2; 1}, 1)
becomes
MID("strawberry", {10; 9; 8; 7; 6; 5; 4; 3; 2; 1}, 1)
and returns {"y"; "r"; "r"; "e"; "b"; "w"; "a"; "r"; "t"; "s"}.
Step 4 - Join characters
The TEXTJOIN function combines text strings from multiple cell ranges.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
TEXTJOIN(, FALSE, MID(B3, SEQUENCE(LEN(B3), , LEN(B3), -1), 1))
becomes
TEXTJOIN(, FALSE, {"y"; "r"; "r"; "e"; "b"; "w"; "a"; "r"; "t"; "s"})
and returns "yrrebwarts".
'MID' function examples
The following 21 articles contain the MID function.
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]
The formula displayed above in cell range D3:D9 extracts a word based on its position in a cell value. For […]
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]
This article demonstrates how to match a value containing both text and digits to ranges. The search value is specified […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Functions in this article
Functions in 'Text' category
The MID function function is one of many functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form