MID in WPS Spreadsheet: Extract Fixed-Position Codes Without Losing Zeros
Extract text by character position in WPS Spreadsheet, with fixed-width codes, boundaries, dates and validation examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: MID extracts a chosen number of characters starting at a numbered position in a text string. Positions start at 1. For example, =MID("BOX-0042-CA",5,4) is expected to return the text 0042.
Availability and version history
- Introduced: documented by 2019-05-07 in the official WPS lesson; original introduction is not established.
- Availability: WPS Office for Windows Personal; WPS 2019 explicitly named in the lesson. This is documentation evidence, not an installed-build guarantee.
- Scope: the exact supported build range is unverified. The cited Windows lesson does not establish equivalent availability in other WPS clients.
- Checked: October 14, 2026 against the function-specific lesson and vendor catalogue. Examples were independently calculated; no new native WPS test was performed.
Syntax and arguments
=MID(text, start_num, num_chars)
text: the source string or a cell containing it.start_num: the first character to extract, counting from one.num_chars: how many characters to request.
All three arguments are required. Spaces and hyphens occupy positions too. The returned value is text; extracting digits does not automatically turn the result into an identifier-safe numeric value or a real spreadsheet date.
The examples use ASCII identifiers so that character positions are easy to inspect. Supplementary Unicode characters, combined accents and byte-oriented functions need separate compatibility checks; this guide does not assume that every WPS client counts them identically.
Example 1: extract the middle field of a fixed-width code
Enter these strings in A2:A4:
| Cell | Source text |
|---|---|
| A2 | BOX-0042-CA |
| A3 | BOX-0187-NY |
| A4 | BOX-9001-TX |
In B2 enter and fill down:
=MID(A2,5,4)
Expected B2:B4: 0042, 0187, 9001. Characters 1–3 are BOX, character 4 is the hyphen, and characters 5–8 are the identifier. Keep this output as text when leading zeros matter.
In C2, =MID(A2,10,2) returns CA. Filling down produces NY and TX. This works because every record follows exactly the same layout; it is not a general-purpose separator parser.
Example 2: understand the end of the string
Use the literal source ABCDE to make the boundary cases reproducible:
| Formula | Expected result | Explanation |
|---|---|---|
=MID("ABCDE",1,2) |
AB | First two characters |
=MID("ABCDE",4,10) |
DE | Stops at the end |
=MID("ABCDE",6,2) |
Empty text | Start lies beyond the end |
=MID("ABCDE",3,0) |
Empty text | Requests no characters |
Empty text means "", not the word “blank” and not the numeric value zero. The official lesson explicitly documents returning the remaining suffix when the requested length runs past the end.
Example 3: separate an encoded date into fields
Put the text RUN-20261014-07 in E2. These formulas extract the year, month and day as text:
=MID(E2,5,4)
=MID(E2,9,2)
=MID(E2,11,2)
Expected results: 2026, 10, 14. The positions come from the fixed schema RUN-YYYYMMDD-NN. Changing the prefix length changes every later position.
If a date value is needed, use the site’s DATE guide to construct it from the extracted fields. Preserve the original code beside the derived fields so that a mistaken schema can be diagnosed. A plausible-looking date is not proof that the identifier was valid.
Advanced example: validate before extracting
The three BOX examples are each 11 characters long. A basic length guard is:
=IF(LEN(A2)=11,MID(A2,5,4),"Review length")
For BOX-0042-CA, the result is 0042. For BOX-42-CA, it is Review length. This catches a shortened middle field rather than silently extracting 42-C.
Length alone does not validate the prefix, separators or allowed characters. For a controlled import, check those rules in separate helper columns. Small checks are easier to explain and audit than a long extraction formula that hides several assumptions.
Errors and troubleshooting
The official WPS lesson specifies #VALUE! when the starting position is below one or the requested character count is negative. Check =MID("ABCDE",0,2) and =MID("ABCDE",2,-1) as diagnostic cases before trusting imported start/count values.
If a result is shifted by one character, write the positions above a sample string and count the separators. If leading zeros are already missing in the source cell, MID cannot recover them. If a formula is displayed as text, check the leading equals sign and cell format before changing the extraction logic.
Practical choices
Use MID when position and width define the field. Use LEFT or RIGHT for a fixed prefix or suffix, and LEN to check expected length. Keep the source, extraction rule and output together during testing. Verify at least a normal record, a shorter record and a malformed record before filling an entire column.
References and verification
- Official WPS MID lesson, published 2019-05-07 (Chinese; Windows Personal scope).
- WPS MID documentation catalogue.
The lesson establishes the function-specific behavior discussed here. The catalogue confirms its listed identity, not an exact current build range. Expected outputs use original examples checked independently; native execution and broader client compatibility remain unverified.