Excel MID Function: Extract Text From the Middle of Text

Excel’s MID function extracts a chosen number of characters from the middle of a text value. With the full text in A2, =MID(A2,5,4) returns the four characters beginning at position 5; for INV-4829-CA, the result is 4829.

What the MID function does

MID is useful when a value contains information in a consistent position or when you can calculate where the desired part begins. For example, product codes may combine a category, a number, and a region. A MID formula can return just the number without changing the original cell. It can also pull characters from names, reference IDs, phone numbers, and other text strings.

MID returns text, even if the extracted characters look like a number. That distinction matters for later calculations: the result 4829 may appear numeric, but Excel treats the characters returned by MID as text until you convert them. Text results are often exactly what you want for identifiers, especially if they contain leading zeros.

The function does not edit the source value. It creates a result in the formula cell, so the original text remains intact. If the source changes, the result updates automatically, provided the formula refers to the changed cell.

MID syntax and character positions

The function has three arguments: MID(text, start_num, num_chars). Text is the cell or string to read. Start_num is the position of the first character to return, counting from the left, with the first character at position 1. Num_chars is how many characters to return. These are positions within the text, not worksheet column numbers or byte counts.

For example, in INV-4829-CA, I is position 1, N is 2, V is 3, the first hyphen is 4, and 4 is 5. To return the four-digit section, the formula uses a starting position of 5 and a length of 4. MID does not skip punctuation or spaces: every character in the string contributes to the position count.

When entering a formula, select the destination cell, type an equals sign and the function, then press Enter. On Windows, Ctrl+Enter fills the selected cell with the entry; on Mac, use Control+Return. To copy a formula down a column, use the fill handle or copy and paste. Relative references such as A2 normally adjust to A3, A4, and so on as you fill down.

Extract text from a fixed position

Fixed-position extraction is the simplest use case. It works well when every entry follows the same layout, such as a fixed-width file or a code whose segments never change length. Suppose A2 contains INV-4829-CA and every code has a four-character number starting at character 5. With the code in A2, =MID(A2,5,4) returns 4829. The starting position and character count are both explicit, so the formula is easy to check.

If a segment starts later, adjust start_num; if it has another length, adjust num_chars. For instance, with serial values in A2 that always begin with two letters followed by six digits, the digits start at position 3. With that layout, =MID(A2,3,6) returns the six characters beginning there. This formula assumes the source values actually follow the stated pattern; MID will not validate that the characters are digits.

Fixed positions can be fragile if entries are not uniform. If one code has an extra prefix character, the formula may still return a result but it will take the wrong section. Before applying a fixed-position formula to a large list, compare several short, long, and unusual source values to confirm that the segment really appears at the same character position.

Find the starting position with SEARCH

When the target is identified by a marker rather than a fixed location, combine MID with SEARCH. SEARCH returns the position of a text string inside another string. It is not case-sensitive, which is convenient when letter case varies. In A2, suppose the value is INV-4829-CA and the wanted section begins immediately after the first hyphen. With that layout, =MID(A2,SEARCH("-",A2)+1,4) returns 4829. SEARCH finds the hyphen at position 4, and adding 1 makes MID start at position 5.

This formula still uses a fixed length of four characters. If the value after the hyphen can have a different length, it is better to find both delimiters and calculate the distance between them. With A2 containing a code in the form prefix-middle-suffix, such as INV-4829-CA, =MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1) returns 4829. The second SEARCH starts looking just after the first hyphen. The difference between delimiter positions, minus one, gives the number of characters between them.

SEARCH can accept a start position, as in that second-search argument. If the marker is absent, SEARCH returns an error, and the combined formula will also return an error. For case-sensitive matching, use FIND in place of SEARCH; FIND uses the same general purpose of locating text, but it distinguishes uppercase and lowercase letters.

Extract text between delimiters

A delimiter is a character or text marker that separates fields, such as a hyphen, slash, comma, or colon. If the text to extract lies between two delimiters, the dynamic-length formula above can adapt to different middle-section lengths. For example, with A2 containing AB-73-XYZ, that formula returns 73; with A3 containing AB-8150-XYZ, filling the formula down returns 8150. This works because the formula calculates the span instead of assuming a fixed number of characters.

For a simple value with a known delimiter and a fixed-length section, a shorter formula may be easier to maintain. If A2 contains EMP:00417 and the five-character identifier always follows EMP:, then with that layout, =MID(A2,5,5) returns 00417. The leading zeros remain because MID returns text. If the identifier length varies, use a delimiter-based method or a newer text function rather than guessing a maximum length.

In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER can make delimiter-based tasks more readable. For example, with A2 containing INV-4829-CA, =TEXTBEFORE(TEXTAFTER(A2,"-"),"-") returns 4829. This gets the text after the first hyphen and then takes what comes before the next one. These functions are not available in every Excel edition; use MID with SEARCH for broader compatibility or when you need to specify an exact character count.

Numbers, dates, and common errors

Because MID returns text, extracted digits may not work directly in arithmetic or numeric sorting. If A2 contains the code INV-4829-CA and B2 contains the extracted text 4829, then with that layout, =VALUE(B2) converts it to the number 4829. The displayed value is the same, but Excel can now use it in calculations. Do not convert identifiers that need leading zeros: numeric conversion would turn text 00417 into the number 417.

MID is not a date-parsing function. If you extract part of a date stored as text, the result remains text and may depend on the source format. Excel dates stored as serial numbers are numeric values, so using MID on a date cell is generally not the right way to retrieve its month or year. Use date functions such as MONTH or YEAR for actual date values, and first confirm whether the source is a real Excel date or text that merely looks like one.

If start_num is less than 1, MID returns a #VALUE! error.
If num_chars is negative, MID returns a #VALUE! error.
If start_num is beyond the end of the text, MID returns an empty string.
If num_chars extends past the end, MID returns the remaining characters rather than an error.
If SEARCH cannot find its marker, a formula that depends on SEARCH returns an error.

To handle missing markers, first decide what the result should mean: blank, a warning, or the original value. You can use IFERROR to provide a fallback, but doing so can hide malformed source data if every error is silently converted to blank. For a data-cleaning workflow, it may be better to expose unexpected values and inspect them rather than suppress the error.

MID compared with LEFT, RIGHT, and newer functions

LEFT returns characters from the beginning of text, while RIGHT returns characters from the end. MID handles a section beginning at a position you specify, so it is the natural choice for an interior segment. If A2 contains INV-4829-CA, LEFT is appropriate for a prefix and RIGHT for a suffix; MID is appropriate for the number in the center. All three return text.

TEXTBEFORE and TEXTAFTER can be more expressive when the task is defined by delimiters rather than positions, but they require Microsoft 365 or Excel 2024. MID is available in older Excel versions as well, making it useful for workbooks shared across different installations. SEARCH or FIND can add flexibility when delimiters move, while fixed-position MID is usually shorter when the layout is guaranteed.

Choose based on the shape of the data, not just formula length. Use MID with fixed numbers when entries have a stable character layout. Use MID with SEARCH or FIND when a marker can move but the desired segment is bounded by known markers. Use TEXTBEFORE or TEXTAFTER in supported versions when a delimiter-based formula is clearer. If the input has inconsistent separators or missing fields, clean or validate the data before relying on any extraction formula.

Frequently Asked Questions

Does MID count from zero or one?
It counts from one. The first character in the text is position 1, so a start_num of 1 begins at the leftmost character.

Can MID extract text after a word or symbol?
Yes. Use SEARCH or FIND to locate the marker, add the number of characters in that marker to get the starting position, and give MID the desired character count. If the length varies, calculate it from a second marker or use TEXTAFTER where available.

Why does MID return a number as text?
MID is a text function, so characters such as 4829 remain text even though they look numeric. Use VALUE if the result needs to participate in arithmetic, but keep it as text when leading zeros or identifier formatting matter.

What happens if the text is shorter than the requested length?
MID returns the characters that remain. If the starting position is past the last character, the result is empty. A negative character count or a start position below 1 causes a #VALUE! error.

Is MID case-sensitive?
MID returns characters from specified positions and does not search for text, so case sensitivity is not relevant to MID itself. SEARCH is not case-sensitive; use FIND when locating a marker must distinguish uppercase from lowercase.

Does MID work in Excel for Mac and Excel for the web?
MID is available in Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. The formula syntax is the same. Ribbon layouts and some newer function availability can differ by platform and version, but the basic MID function does not require a Windows-only feature.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.