How to Extract and Trim Numeric Codes with Excel MID Function
Question details
The user needs to extract a numeric code from a specific position in a text string using the MID function, while properly handling leading or trailing zeroes to return clean numbers or blanks.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting substring numbers from a larger text string where extracted values might have unwanted zeroes (e.g., converting '0934' to 934, '0004' to 4, or handling entirely blank/zero results).
- Observed behavior
- Using a basic MID function returns text with leading/trailing zeroes. The formula needs to output pure numeric values or a blank cell if the extracted result resolves to zero.
Identify the exact starting position and length of the numeric code in your text string, and determine whether you need the final result to be formatted as a mathematical number or left as a text string.
Use the LET Function and Double Unary (--)
Convert extracted text strings into pure numeric values using the double unary operator, and automatically output blanks for zero values.
Click on the cell where you want the extracted number to appear.
Type the formula: =LET(a,--MID(A1,40,4),IF(a=0,"",a)) into the formula bar. Replace A1 with your target cell, 40 with the starting character position, and 4 with the length of the string to extract.
Press Enter to calculate the result. The double minus (--) forces the text into a number, which automatically strips leading zeroes (e.g., '0205' becomes 205). Drag the fill handle down to apply the formula to the rest of your column.
Remove Zeroes Using the SUBSTITUTE Function
If you want to remove zeroes but keep the result formatted as text, you can use the SUBSTITUTE function in combination with MID.
Easily Extract Text and Numbers with WPS Spreadsheet
WPS Spreadsheet fully supports advanced text extraction functions like MID, LET, and SUBSTITUTE, ensuring seamless compatibility with Microsoft Excel workbooks. You can clean, extract, and analyze your data efficiently.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the text strings.
- 2. Enter the extraction formula: Click the target cell and enter the exact same =LET(a,--MID(A1,40,4),IF(a=0,"",a)) formula.
- 3. Apply to the column: Press Enter, then click and drag the small square at the bottom right of the cell to apply the extraction logic to your remaining data.
Frequently Asked Questions
Why does the MID function return numbers as text?
The MID function is classified as a text function in spreadsheet software. It always returns a text string, even if the extracted characters are digits. To use them in calculations, you must convert them using mathematical operations like multiplying by 1, using the double unary (--), or applying the VALUE function.
How do I keep leading zeroes when extracting numbers?
If you need to keep leading zeroes (for example, keeping '0934' exactly as it is), simply use the standard MID function without converting it to a number. For example: =MID(A1,40,4) will keep the text format and preserve the leading zeroes.
What does the double minus (--) do in the formula?
The double minus, known as the double unary operator, is a fast and efficient way to coerce a text string containing numbers into a true numeric value. It functions essentially like multiplying the text by 1 or using the VALUE function, stripping leading zeroes in the process.
Can I use Flash Fill instead of formulas for extracting numbers?
Yes. In many cases, you can type the desired extracted number in the adjacent column and press Ctrl+E (Flash Fill). The spreadsheet will attempt to recognize the pattern and automatically extract the remaining numbers without needing a complex MID formula.
