How to Check Whether Excel Text Contains Unapproved Letters in WPS Spreadsheets
Question details
Validate whether text in one column uses only letters approved in another column, returning Valid when no unapproved letter is found and Invalid otherwise.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has stories in column D and approved letters in column B for each group, and needs to compare each story's characters against the relevant group letter list.
- Observed behavior
- The formula should split the story text into individual characters, compare them with the approved group letters, and return Valid or Invalid.
Ensure the worksheet uses unmerged cells where possible and that approved letters are entered in column B for each group. Confirm your WPS Office version supports the LET, XMATCH, TEXTSPLIT, and FILTER dynamic array functions.
Use a Dynamic LET Formula to Validate Each Character Against Approved Letters
Best when your WPS Spreadsheets version supports dynamic array functions and you need an automatic group-aware check that returns Valid or Invalid.
This formula collects the approved letters above the next group, splits the story text into individual characters, and tests each character against that list. It returns Valid only after removing approved letters leaves just blank spaces.
Click the first cell where you want the result, such as E2, next to the first story row.
Type or paste =LET(letters,$B$2:INDEX(B:B,IFERROR(XMATCH(A2+1,A:A),ROW(A2)+10)-1),IF(TRIM(CONCAT(TEXTSPLIT(D2,FILTER(letters,LEN(letters)>0,""),,FALSE,1)))="","Valid","Invalid")) into the formula bar and press Enter.
Drag the fill handle from the result cell down through the remaining rows so each story is validated against its own group range.
If groups do not use sequential numbers in column A, replace A2+1 and A:A with the actual group-start column and rule so the INDEX range stops above the next group.
Check Excel Text for Unapproved Letters in WPS Spreadsheets
WPS Spreadsheets can run this Excel-compatible validation formula with LET, XMATCH, TEXTSPLIT, and FILTER, so you can keep your story and approved-letter workbook in one free office suite and get Valid or Invalid results quickly.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel-compatible file containing the stories and approved letters.
- 2. Select the result cell: Click the first cell for the validation result, such as E2, next to the first story.
- 3. Apply the validation formula: Paste the LET formula into the formula bar and press Enter to return Valid or Invalid for that row.
- 4. Fill down and review: Drag the fill handle down the column and review the results, adjusting group ranges if required.
Frequently Asked Questions
Why does the formula return #NAME? in WPS Spreadsheets?
The #NAME? error usually means your version does not support LET, XMATCH, TEXTSPLIT, or FILTER. Update WPS Office to a current version that includes dynamic array functions, then re-enter the formula.
How does the formula stop at the correct group’s approved letters?
The XMATCH(A2+1,A:A) part finds the first row of the next group, and INDEX(B:B,...-1) ends the approved-letter range just above it. Adjust A2+1 and A:A if group IDs are different.
Can the text include spaces or punctuation?
Spaces are ignored by the TRIM and CONCAT comparison, but punctuation must be added to the approved letters or removed from the story column for the formula to return Valid.
What should I do if cells are merged?
Unmerge the cells first. Merged cells can prevent the dynamic formula from filling and referencing columns correctly; use an unmerged worksheet layout.
Does this work with Microsoft Excel files?
Yes. WPS Spreadsheets opens .xlsx and .xls files, and the same formula can be used when the file is opened in Excel 365 if dynamic array functions are available.
