Yes. Excel’s MID function can return a variable number of characters when you calculate its num_chars argument from the text, a cell value, or a delimiter position. MID still requires that third argument; it does not automatically detect where a piece of text ends.
What Excel’s MID function does
MID extracts a section of text from a string, starting at a character position you specify. Its syntax is MID(text, start_num, num_chars): text is the source, start_num is where extraction begins, and num_chars is how many characters to return. Character positions start at 1, not 0.
For example, suppose cell A2 contains INV-2026-0457. With that value in A2, the formula =MID(A2,5,4) returns 2026. The fifth character is the first 2, and the formula takes four characters from there. The text has a fixed-length result in this example because the third argument is the number 4.
The important distinction is that the argument itself can be a calculation. MID does not require you to type a literal number for num_chars. If the calculation returns 4 for one row and 7 for another, MID can return different-length results as the formula fills down.
How the three arguments work
The first argument can be a cell reference, a text value, or another formula that produces text. The second and third arguments can also be cell references or calculations. This is what makes MID useful with codes, imported descriptions, and strings whose parts vary in length.
For instance, assume A2 contains Report and B2 contains the starting position 3. The formula =MID(A2,B2,LEN(A2)-B2+1) returns port. LEN counts six characters in “Report”; subtracting the start position and adding 1 gives four characters from position 3 through the end.
LEN is often paired with MID because it measures the length of the source text. The calculation LEN(A2)-B2+1 means “take everything from the start position through the last character.” This is a common way to make the returned length adjust automatically when the text changes.
textidentifies what MID reads.start_numidentifies the first character to include.num_charsdetermines how far the extraction continues.
Make the length vary with the remaining text
When you need the rest of a string from a known position, calculate the count by subtracting the start position from LEN. Assume A2 contains INV-2026-0457; the formula =MID(A2,5,LEN(A2)-4) returns 2026-0457. There are 13 characters in the value, and nine remain from position 5 onward.
This version uses a fixed starting position of 5 but a length that changes if the text in A2 gets longer or shorter. If the start position is stored in B2 instead, use the layout described above and the formula =MID(A2,B2,LEN(A2)-B2+1). For A2 equal to Report and B2 equal to 3, it returns port; changing B2 to 2 returns eport.
Check the start position before using this pattern. If B2 is 0, the formula creates an invalid start position. If B2 is greater than the text length, MID returns an empty string, so the displayed result may look like a blank cell. A blank result is not always evidence that the source cell is blank.
Calculate the length from delimiters
A more flexible task is extracting text between separators when the part can vary in length. Suppose A2 contains North|00482|Open, with vertical bars separating three fields. To return the middle field, use =MID(A2,FIND("|",A2)+1,FIND("|",A2,FIND("|",A2)+1)-FIND("|",A2)-1). It returns 00482.
Here, FIND locates the first bar and the next bar. MID starts one character after the first bar. The count is the distance between the two bars minus one, so the separators themselves are excluded. Because FIND calculates both the start and the length, the formula still works if the middle field changes from five characters to a different length.
Delimiter formulas depend on the structure of the input. If the separator appears more than twice, the formula needs to identify which occurrence marks the field boundary. If delimiters can be missing, FIND returns an error rather than a position. For a one-delimiter case, assume A2 contains text that may or may not include a vertical bar; =IFERROR(MID(A2,FIND("|",A2)+1,LEN(A2)),"") returns the text after the first bar, or an empty string if no bar is found. It is a useful fallback, but a blank result can conceal malformed data, so consider returning a message such as “Check format” when that is more helpful.
If the fields are separated by spaces, punctuation, or another consistent character, the same general method applies: locate the boundaries, then use the difference between their positions as num_chars. For data with inconsistent separators or quoted fields, a simple MID formula may not be enough; cleaning or splitting the source data can be more reliable.
Choose the right extraction method
MID is the right fit when the start position matters and the desired length is fixed or calculated. It is especially useful when you are extracting a middle field, using a position stored in another column, or building the count from LEN and FIND.
Other functions may be clearer for simpler jobs. LEFT returns characters from the beginning, while RIGHT returns characters from the end. For example, if A2 contains North|00482|Open, =LEFT(A2,FIND("|",A2)-1) returns North. That formula uses LEFT because the requested field begins at the first character; MID is not necessary.
For newer Excel versions, TEXTBEFORE and TEXTAFTER can make delimiter-based formulas easier to read. In Microsoft 365 or Excel 2024, if A2 contains North|00482|Open, =TEXTBEFORE(TEXTAFTER(A2,"|"),"|") returns 00482. TEXTAFTER first returns everything after the first bar, then TEXTBEFORE takes the part before the next bar. MID remains useful when you need a position-based approach or must support a version that does not have those functions.
Use MID rather than LEFT or RIGHT simply because a string has a predictable-looking pattern only if the start and length logic is genuinely easier to maintain. A formula that relies on fixed character positions can fail when a code format changes. A delimiter-based formula is often more adaptable when the separators are dependable.
Common errors and data issues
MID returns text. If the extracted characters look like a number, such as 00482, they can still be text and preserve leading zeros. That is usually desirable for postal codes, product IDs, and account identifiers. If you need to do arithmetic with extracted digits, you may need to convert the result to a number, but doing so removes leading zeros and may be inappropriate for identifiers.
Check these issues when a formula gives an unexpected result:
- Wrong start position: MID counts from 1. A separator or leading space counts as a character too.
- Unexpected spaces: A space before or after a field changes character positions and may appear in the result. TRIM can help with ordinary extra spaces, but it does not remove every kind of whitespace.
- Missing separator: FIND returns an error when it cannot locate the requested character. Decide whether to show an error, a blank, or a data-check message.
- Invalid count: A negative
num_charsvalue produces#VALUE!. A count of zero returns an empty string. - Text shorter than expected: If the requested count runs past the end of the string, MID returns the characters that remain. If the start is beyond the end, the result is empty.
Excel formulas in this article use commas between arguments, as is standard in many regional settings. If your Excel installation uses semicolons as the list separator, replace the argument-separating commas with semicolons. The function name and extraction logic do not change.
Build formulas that are easier to maintain
Long delimiter formulas can be difficult to audit because FIND appears several times. Put the first and second delimiter positions in helper columns if the formula will be used across many rows or maintained by other people. For example, one helper can record the first bar’s position and another the next bar’s position; the final MID formula can then use those cells to calculate its start and count. The result is more transparent, and you can inspect the intermediate positions when an input is malformed.
Also decide what should happen when the source format is not as expected. Returning an empty string is convenient for a clean report, while a visible warning is better when missing separators indicate a data-entry or import problem. A formula that hides every error may make a spreadsheet look tidy while quietly leaving out records.
When filling a formula down, use relative references for row-specific text and lock any shared configuration cells with dollar signs if needed. For example, a reference such as $B$1 remains fixed when copied, while A2 adjusts to A3, A4, and so on. Test the formula against short strings, longer strings, and records with missing or repeated separators before relying on the output.
Frequently Asked Questions
Can MID have a variable number of characters?
Yes. MID requires a third argument, but you can make that argument a calculation, such as LEN(A2)-4, or calculate it from delimiter positions. The returned length then changes when the source text or boundary positions change.
Can I leave num_chars blank?
No. The syntax requires num_chars. To return the rest of a string, calculate the remaining length with LEN rather than omitting the argument. For example, if A2 contains the source text and extraction begins at position 5, =MID(A2,5,LEN(A2)-4) returns the remainder from that position.
What happens if num_chars is larger than the remaining text?
MID returns the characters available from the starting position to the end; it does not add padding or return an error just because the requested count is too large. If the start position is beyond the end of the text, the result is empty.
Why does MID return an empty result?
The start position may be beyond the end of the source, the count may be zero, or the source may itself be empty. In a delimiter formula, a missing delimiter can also produce an error that an IFERROR wrapper changes into a blank. Check the inputs and intermediate positions to distinguish these cases.
Is MID case-sensitive?
MID extracts characters by position; it does not search for a word or compare letter case. If you use FIND to calculate a position, FIND is not case-sensitive. Use SEARCH when you need a case-insensitive search with wildcard support, or use a case-sensitive position-finding approach when case must distinguish the match.
Related
- Excel Accounting Solver: What It Does and How to Use It
- Excel Budget Spreadsheet Free? Find Templates and Tips
- Excel Accounting Format: How It Works and When to Use It Properly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power BI DAX Concatenate: Examples and UsageIn Power BI DAX, join two text values with the ampersand operator (&) or the CONCATENATE function; to…
- Round Up in Excel: Use ROUNDUP for Any Number or DecimalTo round a value in Excel away from zero, use =ROUNDUP(A2,0) when the number is in cell A2…
- Excel Minutes Between Two Times: Formula and ExamplesIf the start time is in A2 and the end time is in B2, enter =(B2-A2)*1440 to calculate…
- SUMIF in Excel: Step-by-Step Formula, Examples, and TipsTo total values that match one condition in Excel, use SUMIF. If categories are in A2:A7 and amounts…
- Search Excel Shortcut Keys: Find Cells, Values, and FormulasIn Excel for Microsoft 365 on Windows, press Ctrl+F, enter the word, number, or phrase you want, then…
- Excel F4: How to Lock Cell References in FormulasTo lock a cell reference in an Excel formula, edit the formula, place the cursor in or select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.