How to Extract Text Patterns from Excel Cells with Formulas in WPS Spreadsheets
Question details
The user needs to reliably extract space-separated text values from column E into columns F, G, and H in a Cognos report workbook, using formulas that work across many rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with Cognos report files opened in a spreadsheet application and wants a formula-based way to split inconsistent text patterns instead of manually copying values.
- Observed behavior
- The current formulas or recognition do not consistently identify the text pattern between report files, so the required values are not extracted reliably.
Check whether your WPS Spreadsheets version supports dynamic array formulas by typing =TEXTSPLIT(E2," ") in an empty cell. If an #NAME? error appears, use the SUBSTITUTE-based formulas in the second solution.
Use TEXTSPLIT and INDEX to Extract Values by Position
Use this if your WPS Spreadsheets or Microsoft Excel version supports dynamic array formulas and TEXTSPLIT; it is the shortest and most flexible method.
TEXTSPLIT splits a cell by a delimiter such as a space, and INDEX selects the required segment. You can change the index number to extract any space-separated value from the cell.
Click cell G2, enter =INDEX(TEXTSPLIT(E2," "),LEN(E2)-LEN(SUBSTITUTE(E2," ",""))+1), and press Enter to return the last space-separated value from E2.
Click cell H2, enter =INDEX(TEXTSPLIT(E2," "),3), and press Enter to return the third space-separated value from E2.
Select cells G2:H2, then drag the fill handle down or double-click it to apply the formulas to the remaining rows.
Use Classic SUBSTITUTE and TRIM Formulas When TEXTSPLIT Is Not Available
Use this on older Excel or WPS Spreadsheets versions where TEXTSPLIT or dynamic array functions are not supported.
Extract Inconsistent Text Patterns in WPS Spreadsheets
Open your Cognos report in WPS Spreadsheets and use Excel-compatible TEXTSPLIT or SUBSTITUTE formulas to split values into separate columns, then save the workbook as a normal .xlsx file.
- 1. Download and open WPS Office: Install WPS Office Free, open WPS Spreadsheets, and load the Cognos report .xlsx file that contains column E.
- 2. Select the formula cells: Click G2 for the last value and H2 for the third value, or use F2 if you need to extract the first value with =INDEX(TEXTSPLIT(E2," "),1).
- 3. Enter the appropriate formula: In G2 enter =INDEX(TEXTSPLIT(E2," "),LEN(E2)-LEN(SUBSTITUTE(E2," ",""))+1); in H2 enter =INDEX(TEXTSPLIT(E2," "),3).
- 4. Fill down and save: Drag the fill handle from G2:H2 down through all rows, then save the workbook as .xlsx to keep the formulas.
Frequently Asked Questions
Can I use TEXTSPLIT in WPS Spreadsheets if I don't have Microsoft 365?
In many current WPS Spreadsheets versions, yes. Type =TEXTSPLIT(E2," ") in an empty cell; if you see #NAME?, use the classic TRIM/RIGHT/SUBSTITUTE and TRIM/MID/SUBSTITUTE formulas instead.
Why is the text pattern not recognized consistently between Cognos report files?
The files may contain different spacing, delimiters, or hidden characters. Use TRIM to remove leading and trailing spaces and confirm the actual delimiter, then replace the space in the formula with that delimiter if needed.
How do I extract the last space-separated value without TEXTSPLIT?
Use =TRIM(RIGHT(SUBSTITUTE(E2," ",REPT(" ",255)),255)) in G2 and fill the formula down.
How do I extract the third space-separated value in older Excel or WPS?
Use =TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",255)),512,255)) in H2 and fill down.
How do I apply these extraction formulas to many rows at once?
Select the cells containing the formulas, hover over the bottom-right corner until the fill handle appears, and double-click it or drag it down through the last data row.
