How to Parse Text into Separate Excel Worksheets with Power Query in WPS Office
Question details
The user needs to parse a source text file into separate worksheets in WPS Office Spreadsheets, creating RLF and BEAM_FAILURE tabs based on the Purpose value in each message and presenting the results as a clean table.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A text file contains structured message rows with Purpose values such as RLF and BEAM_FAILURE, and the user wants to split those rows into dedicated worksheets for easier review.
- Observed behavior
- The data is currently contained in one text file and has not yet been separated into RLF and BEAM_FAILURE worksheets or converted into a fully tabular format.
Make sure the source text file uses a consistent layout and that the rows beginning with "Purpose =" can be reliably identified. In WPS Office Spreadsheets, open a blank workbook and confirm that the Data tab includes the From Text/CSV or Power Query import command.
Use Power Query to Filter, Fill Down, Split, and Load Separate Worksheets
Use this method when you want a repeatable Power Query workflow that separates the text file into RLF and BEAM_FAILURE worksheets based on the Purpose value.
Power Query is ideal for this task because it records a repeatable sequence of transformation steps. The key pattern is to create a Purpose column from the "Purpose =" lines, fill that value down so every detail row is labeled, remove unnecessary rows, split the remaining text into columns, and then load separate filtered results.
In WPS Office Spreadsheets, go to the Data tab, choose Get Data > From File > From Text/CSV, select the source text file, and then click Transform Data to open the Power Query Editor.
In the Power Query Editor, select the first column, open its filter menu, and filter for rows that begin with "Purpose =".
With the column still selected, go to Transform or Add Column and choose Extract > Text After Delimiter. Enter "=" as the delimiter and name the new column Purpose.
Right-click the Purpose column header and choose Fill > Down so each following message row receives the current RLF or BEAM_FAILURE value.
Use Remove Rows > Remove Blank Rows, select the remaining text column, and then use Transform > Trim followed by Split Column > By Delimiter to create the tabular fields.
In the Queries pane, right-click the transformed query and choose Reference. Filter the Purpose column to RLF, then use Close & Load To and select Worksheet. Repeat the same process with another referenced query filtered to BEAM_FAILURE.
Alternative: Import as a Table and Separate by Filter with a Helper Column
Use this approach for a small, one-time file when you do not need a reusable Power Query query.
Split a Text File into RLF and BEAM_FAILURE Worksheets with WPS Office
Use Power Query in WPS Office Spreadsheets to read the text file, extract the Purpose value, fill it down, split the message text into columns, and load RLF and BEAM_FAILURE into separate worksheets without manually copying rows.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open the Spreadsheets application, and create a new blank workbook.
- 2. Connect to the source text file: Go to the Data tab, choose Get Data > From File > From Text/CSV, and select the text file you want to parse.
- 3. Transform the data: In Power Query Editor, filter rows starting with "Purpose =", extract the text after "=" into a Purpose column, fill the value down, and remove blank rows.
- 4. Split and load separate worksheets: Reference the transformed query for RLF and another for BEAM_FAILURE, filter each one by the Purpose column, and use Close & Load To to place the results on separate worksheets.
- 5. Refresh when needed: After the source text file changes, click Data > Refresh All or right-click the query and choose Refresh to update the worksheets.
Frequently Asked Questions
Can WPS Office open the Power Query Editor for a text file?
Yes. In WPS Office Spreadsheets, go to Data > Get Data > From File > From Text/CSV, select the text file, and choose Transform Data to open the Power Query Editor.
How do I fill the Purpose value down in Power Query?
Right-click the Purpose column header and choose Fill > Down. This carries the current RLF or BEAM_FAILURE value down until the next "Purpose =" row is encountered.
Why should blank rows be removed before splitting the text column?
Blank rows produce null cells and can make the split result uneven. In the Power Query Editor, use Remove Rows > Remove Blank Rows before you trim and split the message text.
Can RLF and BEAM_FAILURE be loaded into separate worksheets automatically?
Yes. Create two referenced queries from the transformed query, filter one to Purpose equals RLF and the other to BEAM_FAILURE, then use Close & Load To to load each query to its own worksheet.
Does this Power Query process work when the source text file is updated?
Yes. Keep the text file path unchanged and refresh the queries with Data > Refresh All or by right-clicking the query and choosing Refresh.
