Extracting data from the beginning of a string (using LEFT) or the end of a string (using RIGHT) is relatively straightforward in Microsoft Excel. However, dealing with complex database outputs often requires you to extract data trapped precisely in the middle of a string. For example, if a system generates the string “TX-88492-Pending”, and you only need the 5-digit invoice number (“88492”) from the middle, you must use the highly precise MID function.
How the MID Function Works
Unlike other text formulas, the MID function requires three distinct arguments. You must tell it where the text is, exactly where to begin slicing, and exactly how many characters to slice before stopping.
The syntax is: =MID(text, start_num, num_chars)
- text: The cell containing the full string (e.g., A2).
- start_num: The exact character position where the extraction should begin, counting from the left edge. (e.g., the 4th character).
- num_chars: The total number of characters you want to pull out.
How to Extract a Fixed Middle Value
If your dataset is perfectly standardized—for example, if the prefix is always exactly 3 characters long (“TX-“) and the invoice number is always exactly 5 digits long—deploying the formula is simple.
- Create a blank column next to your data (e.g., Column B).
- Click into cell B2 and type:
=MID(A2, 4, 5) - Press Enter.
In this example, Excel skips the first 3 characters (“TX-“), begins the extraction on the 4th character (the “8”), and pulls exactly 5 characters total, cleanly outputting “88492”. You can then double-click the Fill Handle to apply this logic to the rest of the column.
Dynamic Extraction with the FIND Function
Hardcoding the start_num is dangerous if your prefixes vary in length. If row 1 is “TX-88492” (prefix length 3) but row 2 is “CALIFORNIA-99123” (prefix length 11), a hardcoded =MID(A2, 4, 5) will completely ruin the California data, extracting “IFORN” instead of the numbers.
To dynamically find the middle data, you must instruct Excel to search for the hyphen (-) that separates the prefix from the number.
=MID(A2, FIND("-", A2) + 1, 5)This advanced formula uses the FIND function to calculate the exact numerical position of the first hyphen in the cell. It then adds 1 to that number (so it starts on the digit immediately after the hyphen), and then passes that dynamic starting position to the MID function. This guarantees that your 5-digit invoice number is extracted perfectly every single time, completely regardless of how long the state prefix happens to be.