Find GTIN Values Containing Invalid Characters in Excel with WPS Spreadsheets
Question details
Identify GTIN values in a WPS Spreadsheets workbook that contain characters other than uppercase letters A-Z, digits 0-9, and the plus sign.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a list of GTIN values and needs to flag entries that contain invalid characters according to the allowed set A-Z, 0-9, and +.
- Observed behavior
- Some GTIN values may include invalid characters that are not easy to spot during manual review.
Open the workbook in WPS Spreadsheets and confirm whether your organization allows VBA macros for the validation method. Use only trusted files and check the macro security setting before running any VBA code.
Use a VBA regular expression function to flag GTIN values with invalid characters
Best for large or recurring GTIN lists because it applies a reusable pattern check in WPS Spreadsheets with VBA macros enabled.
The VBA function uses the regular expression pattern ^[A-Z0-9+]+$ to test each GTIN value. If any character is outside A-Z, 0-9, or the plus sign, the function returns Invalid; otherwise it returns Valid. Enable macros only for trusted workbooks that pass your organization’s security policy.
In WPS Spreadsheets, press Alt+F11 or click Developer > Visual Basic to open the Visual Basic for Applications editor.
In the Project pane, right-click the workbook name, select Insert > Module, and paste a VBA function named ValidateGTIN that uses RegExp with the pattern ^[A-Z0-9+]+$ and returns Valid or Invalid.
Return to the worksheet, click the first empty cell beside a GTIN value, enter =ValidateGTIN(A2), and drag the fill handle down through all GTIN rows.
Select the helper column header, click Data > AutoFilter, and filter for Invalid to display only GTIN values containing characters outside A-Z, 0-9, and +.
Use a formula-based character check when VBA macros are not allowed
Use this method in WPS Spreadsheets versions that support dynamic array formulas if you cannot enable macros or prefer not to run VBA code.
Find GTIN Values with Invalid Characters in WPS Spreadsheets
WPS Spreadsheets lets you validate GTIN values with a trusted VBA RegExp macro or a formula-based character check. You can quickly isolate GTIN entries that contain invalid characters while working with familiar .xlsx and .xlsm files.
- 1. Download and install WPS Office: Click Download WPS Office Free and install the latest version on your Windows or Mac computer.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the .xlsx or .xlsm file that contains the GTIN values.
- 3. Enable trusted macros or use the formula method: For the VBA RegExp method, go to File > Options > Trust Center > Macro Settings and allow trusted macros, then open the VBA editor with Alt+F11.
- 4. Run the validation and review the results: Apply the ValidateGTIN function or the formula helper column, then filter for Invalid to isolate GTIN values with characters outside A-Z, 0-9, and +.
Frequently Asked Questions
Does this work in WPS Spreadsheets if my file was created in Microsoft Excel?
Yes. WPS Spreadsheets opens .xlsx and .xlsm workbooks and supports VBA macros; enable macros only when the file is from a trusted source.
Can I find invalid GTIN values without enabling macros?
Yes. You can use a formula helper column with LET, MID, SEQUENCE, and CODE if your version of WPS Spreadsheets supports dynamic arrays.
What characters are considered valid in this GTIN check?
Valid characters are uppercase letters A through Z, digits 0 through 9, and the plus sign. Any other character is flagged as invalid.
How do I enable macros in WPS Spreadsheets?
Go to File > Options > Trust Center > Macro Settings and choose the appropriate macro setting, or click Enable Macros when opening a trusted workbook.
How do I filter for only the invalid GTIN entries?
After applying the helper formula, select the header row, click Data > AutoFilter, and filter the helper column for Invalid.
