A final line break is accepted
Use the exact length condition as well as the regex anchors.
Normalize and review structured reference codes
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Keep nonempty A2:A9 records in source order, replace NBSP with ordinary spaces, then TRIM ordinary spaces. Validate the cleaned source as exactly two ASCII letters, one hyphen and four ASCII digits, with length seven. Display UPPER(cleaned), Ready or Review, and the four-character text suffix only for Ready records. A cleaned blank record is still Review. If there are no source records, return No records.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:I12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Create a three-column audit that validates a declared ASCII code shape before extracting a four-character suffix.
THE QUICK ANSWER01
Create a three-column audit that validates a declared ASCII code shape before extracting a four-character suffix.
ƒx
=LET(records,FILTER(A2:A9,A2:A9<>"",""),cleaned,TRIM(SUBSTITUTE(records,CHAR(160)," ")),valid,REGEXTEST(cleaned,"^[A-Za-z]{2}-[0-9]{4}$")*(LEN(cleaned)=7),normalized,UPPER(cleaned),IF(SUMPRODUCT(--(A2:A9<>""))=0,"No records",HSTACK(normalized,IF(valid,"Ready","Review"),IF(valid,TEXTAFTER(normalized,"-",1,0,0,""),""))))| Normalized code | Review state | Four-digit text |
|---|---|---|
| AA-0007 | Ready | 0007 |
| BB-0000 | Ready | 0000 |
| BAD | Review | "" |
| CC-0099 | Ready | 0099 |
| DD--1234 | Review | "" |
| EE-123 | Review | "" |
| FF-12345 | Review | "" |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A declared format, not universal validity
This task defines a local import rule. Ready means the cleaned source has the stated seven-character ASCII layout; it does not prove that an identifier was issued or exists in another system.
Validate before case expansion
ASCII lowercase is allowed in the source and becomes uppercase in the display. A source such as ß-1234 is outside the two-ASCII-letter rule even if uppercasing expands it. The validation mask is calculated before UPPER.
Keep the suffix as text
aa-0007 becomes AA-0007, Ready, 0007. BB-0000 retains 0000. Converting the suffix with VALUE loses its fixed-width identity. Duplicates remain separate records.
Regex has a scope
The anchored character classes and fixed repetitions used here belong to the syntax shared by the browser engine and Excel PCRE2. Excel REGEXTEST requires a supporting Microsoft 365 version. Full PCRE2 behavior and native-client recalculation remain unverified.
Keep missing data distinct
A blank source cell is omitted, but a source made of spaces or NBSP is retained and becomes an empty normalized string with Review. A tab or line break is not ordinary space cleanup and prevents Ready under this rule.
Read the full step-by-step walkthrough
- Keep a source record even if cleanup later empties it
Filter only genuinely empty source cells. A space-only source record must remain available for review.
- Declare whitespace cleanup
Replace NBSP with ordinary space, then use TRIM. Internal spaces, tabs and line breaks are not silently deleted.
- Check the exact alphabet and layout
The pattern accepts two ASCII letters, a literal hyphen and four ASCII digits. It checks the cleaned source before case expansion.
- Check exact length as a separate condition
LEN=7 rules out an extra final line break and any extra characters. Multiply the two conditions to require both.
- Normalize display without treating it as proof
UPPER supplies a consistent display. A non-ASCII source that changes when uppercased still cannot bypass the earlier validation.
- Extract only the accepted suffix
A Ready code receives four text digits, including leading zeroes. Review records keep an empty suffix, while the original source remains available.
Things to watch
Unicode lookalikes become Ready
The declared alphabet is ASCII. Validate the cleaned source before display normalization.
0007 becomes numeric 7
Use text extraction without VALUE. The last column is a four-character identifier component.
About this example
The original bounded datasets, typed outputs and independent rules are checked in the browser engine. Native Excel/Sheets recalculation, editing and save/reopen are separate pending checks. Regex uses the syntax shared by the pinned browser engine and PCRE2; full PCRE2 support is not claimed.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.