MID
Pull characters from the middle of a text string with MID, starting at any position you choose.
Spreadsheet editor
Spreadsheet editor
Syntax
=MID(Text, Start_num, Num_chars)
Returns: Text Arguments
| Argument | Required | Description |
|---|---|---|
| Text | Yes | The text string you want to pull characters from. |
| Start_num | Yes | The position of the first character to extract. The first character in the text is position 1. |
| Num_chars | Yes | How many characters to return, starting at Start_num. |
About
MID extracts a set number of characters from a text string, starting at the position you specify. Give it the text, the position to start from, and how many characters to grab, and it returns just that slice. It always counts from the left, so position 1 is the first character.
Use MID when the part you need sits in the middle of a value, like a region code buried in a product ID or the month inside a date stamp. For text at the very start or end, LEFT and RIGHT are simpler. MID really shines when you pair it with FIND or SEARCH to locate a delimiter, then pull out the text between two markers.
MID always returns text, even when the result looks like a number. If you plan to do math with the output, wrap it in VALUE to convert it back to a number first.
Use MID when the part you need sits in the middle of a value, like a region code buried in a product ID or the month inside a date stamp. For text at the very start or end, LEFT and RIGHT are simpler. MID really shines when you pair it with FIND or SEARCH to locate a delimiter, then pull out the text between two markers.
MID always returns text, even when the result looks like a number. If you plan to do math with the output, wrap it in VALUE to convert it back to a number first.
Exercises using MID
Parse full address
IntermediateExtract city from a full address string (assume consistent format).
Open exerciseComplex text parsing
AdvancedParse inconsistently formatted addresses into separate columns (street, city, state, zip).
Open exerciseExtract data from mixed format
AdvancedExtract product codes from descriptions with mixed labels and positions.
Open exerciseExamples
Grab the rest of a string
Set a large Num_chars to pull everything from position 4 to the end. Watch the AB row: when the start position runs past the text, MID returns an empty string instead of an error. Try shortening the codes to see it adjust.
Spreadsheet editor
Why the result will not do math
See how the month MID pulls out is text, not a number, so ISNUMBER reports FALSE. Wrap it in VALUE to convert it, then change a date code and watch both columns update.
Spreadsheet editor
Extract a domain with MID and FIND
Use FIND to locate the @ sign, then let MID grab everything after it. Edit an email address and watch MID pull the correct domain no matter how long the address is.
Spreadsheet editor
Watch out for
⚠
Counting from zero instead of one
MID starts counting at 1, not 0. Using a Start_num of 0 (or any value below 1) returns a #VALUE! error.
→ Always treat the first character as position 1. The space counts as a character too, so include spaces when you count.
⚠
Treating the result as a number
MID always returns text, so a result like "06" or "2024" will not behave as a number in calculations.
→ Wrap the formula in VALUE to convert the text to a real number you can do math with.
Tips & notes
MID is case-sensitive only in the sense that it returns characters exactly as they appear, so the case of the original text is preserved. If Start_num is longer than the text, MID returns an empty string rather than an error. If Num_chars runs past the end of the text, MID simply returns everything from Start_num to the end. For double-byte character sets, MIDB counts by bytes instead of characters.
Common questions
What is the difference between MID, LEFT, and RIGHT?
Can MID extract characters from a number?
Yes. Excel converts the number to text first, so =MID(20240615, 5, 2) returns "06". The result is text, so wrap it in VALUE if you need a number back.
How do I extract everything after a specific character?
Combine MID with FIND to locate the character, then use a large Num_chars (like LEN(A2)) so MID grabs the rest of the string. For example, =MID(A2, FIND("@", A2) + 1, LEN(A2)) returns the domain from an email address.