Clean and Standardize Identifier Text in WPS Spreadsheets with Power Query
Question details
The user needs to process about 58,000 text records, remove nonnumeric characters, add leading zeros to identifiers, and combine them into fixed-length values based on multiple record patterns.
- Product
- WPS Spreadsheets with Power Query / Get & Transform
- Device & OS
- not provided
- Scenario
- Import a large text file, filter relevant record types, parse text between braces, clean and pad identifiers, and combine the results into fixed-length strings.
- Observed behavior
- Raw identifiers contain nonnumeric characters and inconsistent lengths that need to be standardized before they can be combined.
Confirm that your source text file uses consistent record formatting and identify the exact record prefix to filter, such as #TRANS. Power Query is case-sensitive, so verify whether values like #TRANS and #Trans should be treated separately.
Use Power Query to Parse, Clean, Pad, and Combine Identifier Values
Use this solution to process the entire text file in one repeatable query without manually editing thousands of rows.
Power Query is well suited for large text files because the steps are recorded once and can be refreshed when new records are added.
The results depend on consistent input formatting, so check the parsed values against real records before applying the query to the full dataset.
In WPS Spreadsheets, go to Data > Get Data > From Text/CSV and select the text file. Click Transform Data to open Power Query Editor.
Select the column that contains the record text. Go to Home > Keep Rows > Keep Rows, choose Begins With, and enter the exact pattern such as #TRANS.
Use Add Column > Extract > Text Between Delimiters. Set the start delimiter to { and the end delimiter to } to isolate the identifier block.
On the Add Column tab, click Custom Column and enter a formula such as Text.Select([IdentifierColumn], {"0".."9"}) to keep only digits.
Create another Custom Column using Text.PadStart([CleanedColumn], 8, "0") to standardize length, or use Text.Combine to join the cleaned identifier parts into one fixed-length value.
Click Home > Close & Load. Review the output table against the source records and refresh after adding more data.
Verify Case-Sensitive Filters and Refresh the Output with Actual Records
Use this if some records are incorrectly replaced or skipped because the text pattern, spacing, or capitalization differs from the filter.
Standardize Large Identifier Datasets with WPS Spreadsheets Power Query
WPS Spreadsheets provides Power Query / Get & Transform tools that let you clean, pad, and combine thousands of identifier records in a repeatable workflow instead of editing cells manually.
- 1. Open WPS Spreadsheets: Launch WPS Spreadsheets and open a blank workbook or your existing data workbook.
- 2. Import the text file into Power Query: Click Data > Get Data > From Text/CSV and choose the file that contains the 58,000 records.
- 3. Apply cleaning and padding formulas: In Power Query Editor, use custom columns with Text.Select to remove nonnumeric characters and Text.PadStart to add leading zeros.
- 4. Filter and combine the identifiers: Filter the relevant rows by the exact record pattern, then concatenate the cleaned values into the required fixed-length string.
- 5. Load the cleaned result: Click Close & Load to output the transformed dataset into WPS Spreadsheets.
Frequently Asked Questions
Does WPS Office include Power Query?
Yes. WPS Spreadsheets includes Power Query / Get & Transform tools under the Data tab so you can import and transform large text or CSV files without manual editing.
How do I remove nonnumeric characters from a column in Power Query?
In Power Query Editor, select the column, go to Add Column > Custom Column, and enter a formula such as Text.Select([Column], {"0".."9"}).
How do I add leading zeros to make identifiers a fixed length?
Use a custom column with Text.PadStart([Column], 8, "0"), replacing 8 with the required total length for your identifier.
Why are #TRANS and #Trans treated as different values?
Power Query text filters and comparisons are case-sensitive, so values with different capitalization are treated as separate records.
How do I refresh the cleaned output after adding more source records?
Save the updated source file, return to the WPS Spreadsheets workbook, right-click the output table, and select Refresh.
