How to Match Text at the Beginning of a String and Split It in WPS Office Excel
Question details
The user needs to match a maintained cut-down string against the beginning of an original string, return the corresponding improved string in one column, and return the remaining text in another column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with lookup tables that contain shortened text prefixes and wants to split original text values into a matched replacement prefix and the leftover text.
- Observed behavior
- Formula results are inconsistent or incorrect because cut-down prefix entries contain trailing dashes or spaces and the lookup table is not normalized.
Check that your cut-down prefix entries have no trailing dashes, spaces, or hidden characters. Also confirm whether your version of WPS Office Spreadsheets supports dynamic array formulas so you can choose between a modern formula and a legacy array formula.
Clean the Prefix Table and Use a Dynamic Array Formula
Use this method when your WPS Office version supports dynamic arrays; it returns both the improved prefix match and the remaining text without manual Ctrl+Shift+Enter entry.
The formula tests each maintained prefix against the beginning of the original string with LEFT and LEN. Cleaning trailing characters first prevents the mismatches described in the original issue.
Select the cut-down prefix column, press Ctrl+H to open Find and Replace, enter a trailing dash after a space in Find what, leave Replace with empty, and click Replace All. Repeat for other trailing characters until the prefixes match exactly.
In the first output cell for the Improved String column, enter =LOOKUP(2,1/(LEFT(A2,LEN($D$2:$D$10))=$D$2:$D$10),$E$2:$E$10) and press Enter. Adjust the ranges to match your lookup table.
In the next column, enter =MID(A2,LEN(LOOKUP(2,1/(LEFT(A2,LEN($D$2:$D$10))=$D$2:$D$10),$D$2:$D$10))+1,255) and press Enter to return the leftover text.
Select both formula cells and drag the fill handle down through the data range to apply them to every original string.
Use an Array Formula in Older WPS Office Versions
Use this if your WPS Spreadsheets version does not support dynamic array formulas and requires legacy array entry.
Match and Split Text Prefixes in WPS Spreadsheets
WPS Office Spreadsheets gives you the Excel functions needed to match a maintained prefix against the beginning of a text string and split the remaining text into separate columns. Clean your lookup range, enter the formula, and copy it across rows.
- 1. Download and open WPS Office Spreadsheets: Go to WPS.com, download WPS Office for free, open WPS Spreadsheets, and load the workbook that contains the original strings and lookup table.
- 2. Clean the lookup table: Select the cut-down prefix column, press Ctrl+H to open Find and Replace, remove trailing dashes or spaces, and confirm all prefix entries match the beginning of the original strings.
- 3. Enter the improved string formula: In the first Improved String column, enter =LOOKUP(2,1/(LEFT(A2,LEN($D$2:$D$10))=$D$2:$D$10),$E$2:$E$10). Adjust the range to your table and press Enter or Ctrl+Shift+Enter depending on your WPS version.
- 4. Enter the remaining text formula: In the remaining text column, enter =MID(A2,LEN(LOOKUP(2,1/(LEFT(A2,LEN($D$2:$D$10))=$D$2:$D$10),$D$2:$D$10))+1,255) and confirm it.
- 5. Copy the formulas down: Drag the fill handle down through the output columns to match and split all original string rows.
Frequently Asked Questions
Why does the formula return #N/A even when the beginning of the text matches?
The most common cause is a trailing dash or extra space in the cut-down prefix column. Select the prefix cells, press Ctrl+H, replace trailing characters with nothing, and make sure the lookup range is consistent.
How do I get the remaining text after the matched prefix?
Use the MID function with the matched prefix length. In the combined formula, the matched prefix is returned by the LOOKUP portion, and LEN calculates where the remaining text starts.
Do I need to press Ctrl+Shift+Enter in WPS Office?
Only if your WPS Office version does not support dynamic array formulas. In versions that support dynamic arrays, press Enter. In legacy versions, confirm array formulas with Ctrl+Shift+Enter.
Can this method handle prefixes of different lengths?
Yes. The formula compares LEFT(A2,LEN(prefix_range)) for each prefix, so it checks different lengths. Ensure the prefix list is clean and contains no trailing characters that prevent exact beginning matches.
Can WPS Office open and save the workbook after using these formulas?
Yes. WPS Office Spreadsheets is compatible with Microsoft Excel .xlsx files, so you can open, edit, save, and share the workbook without losing the formulas.
