How to Split Complex Product Text into Two Columns in WPS Office Spreadsheets
Question details
Separate combined product text into two columns—one for the product description and one for the model-code list—when the text lacks a consistent delimiter.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has product rows such as "Original Canon Case LED IR001 IR002" and needs the description split from the IR-prefixed model codes.
- Observed behavior
- Simple splitting by spaces creates too many columns or breaks the product description, so a pattern-based or prefix-based split is required.
Before you split the data, check whether the model codes always begin with the same prefix, such as IR, and locate where that prefix first appears. If the prefix also appears inside product descriptions, target the space before the prefix or review the output carefully to avoid incorrect splits.
Split Text by Model-Code Prefix Using Formulas in WPS Office Spreadsheets
Use this when the model codes start with a consistent prefix such as IR and you want to keep both the description and the full code string.
This formula method searches for the first occurrence of the space plus the model-code prefix and extracts the text before it as the description and the text from the prefix onward as the code list. It avoids removing the prefix, unlike entering the prefix as a Text to Columns delimiter.
In your WPS Office Spreadsheets worksheet, confirm the prefix that starts the model codes, for example IR, and note the source column.
In a blank column, enter =TRIM(LEFT(A2,FIND(" IR",A2)-1)) and press Enter. Replace A2 with the first cell of your combined text.
In the next blank column, enter =MID(A2,FIND(" IR",A2)+1,LEN(A2)) and press Enter to return the code list starting at IR.
Drag the fill handle or double-click it to apply the formulas to the remaining rows, then review the split columns for overlapping patterns.
Use Google Sheets REGEXEXTRACT for a Pattern-Based Split
Use this when you work in Google Sheets and the same text pattern repeats in every row, so a single array formula can split all rows at once.
Split Product Text into Two Columns Free with WPS Office Spreadsheets
WPS Office Spreadsheets can open your Excel file, locate the model-code prefix, and split descriptions from codes using formulas or Text to Columns—without changing your original data structure.
- 1. Download WPS Office: Go to the official WPS Office website and click Download to install the free desktop version.
- 2. Open the Excel file in WPS Spreadsheets: Launch WPS Office Spreadsheets and open the .xlsx workbook containing the product text.
- 3. Apply the split: Select your data and use Data > Text to Columns or enter the LEFT/FIND formula described above to separate the product description from the model codes.
- 4. Save the result: Save the workbook as .xlsx to retain compatibility with Microsoft Excel and Google Sheets.
Frequently Asked Questions
What if the model code does not start with IR?
Replace IR in the formula or regex with the actual prefix. For example, use =MID(A2,FIND(" SKU",A2)+1,LEN(A2)) if the code list starts with SKU, or use Text to Columns with that prefix as the delimiter if you do not need to keep the prefix.
Can I split the text when the prefix appears inside the product description?
Yes. Target the space before the prefix by using FIND(" IR",A2) rather than FIND("IR",A2). Then review rows where the description contains the same phrase to prevent incorrect splits.
How do I split all rows at once?
In WPS Office Spreadsheets, fill the formulas down the entire column. In Google Sheets, use ARRAYFORMULA with REGEXEXTRACT to spill results for the full range.
Does Text to Columns keep the prefix?
If you use the prefix as the delimiter, the delimiter is usually removed. To keep the prefix, use the formula method with FIND and MID instead.
