How to Split Text Using the Earliest of Several Delimiters in WPS Office Excel
Question details
The user needs a formula method in WPS Office Excel to split text at whichever of several delimiters appears first when TEXTSPLIT is unavailable and delimiter positions are inconsistent.

- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A user has text strings containing multiple possible delimiters, such as IR, CS, or I5, and needs to extract the text before or after the first delimiter that appears.
- Observed behavior
- TEXTSPLIT is unavailable and inconsistent delimiters prevent simple fixed-position splitting; a formula must detect the earliest delimiter and return the surrounding text.
Before writing the formula, keep the source text in one column and note the exact delimiter values and lengths. The LEFT and RIGHT offsets depend on whether your delimiters include extra spaces or have different character lengths.
Return Text Before the Earliest Delimiter with SEARCH, IFERROR, MIN, and LEFT
Use this formula when you need the portion that appears before the first matching delimiter among several possible separators.
SEARCH locates each delimiter and returns its starting position. IFERROR replaces missing delimiters with a very large number, and MIN picks the smallest position, which corresponds to the delimiter that appears first. LEFT then returns all characters before that position.
Click the cell where you want the extracted text, for example B3, next to the source text in A3.
Type =TRIM(LEFT(A3,MIN(IFERROR(SEARCH("IR",A3),9^99),IFERROR(SEARCH("CS",A3),9^99),IFERROR(SEARCH("I5",A3),9^99))-1)) and press Enter.
Drag the fill handle from B3 down the column to apply the formula to other rows that use the same delimiters.

Return the Remaining Text After the Earliest Delimiter with RIGHT and LEN
Use this formula when you need the portion after the first matching delimiter instead of the prefix.
Split Text at the Earliest Delimiter in WPS Office Excel
WPS Office Excel can directly perform this text split using the same SEARCH, IFERROR, MIN, LEFT, RIGHT, and LEN functions you would use in Excel. Open your workbook in WPS Office Excel, enter the formula, and apply it to the whole column.
- 1. Download and open WPS Office Excel: Download WPS Office Free from the official site, launch WPS Office Excel, and open the workbook containing the text you need to split.
- 2. Prepare the source data: Confirm that the text is in a column such as A and that the delimiter values, such as IR, CS, and I5, are consistent enough for the formula to evaluate.
- 3. Enter the split formula: In the result cell, enter the LEFT/MIN/IFERROR formula for the prefix or the RIGHT/LEN formula for the remaining text, then press Enter.
- 4. Fill the formula down: Drag the fill handle down the result column so WPS Office Excel applies the split logic to every row.
- 5. Save the workbook: Save the file as .xlsx to keep full compatibility with Microsoft Excel while preserving the formulas.

Frequently Asked Questions
Why does the formula use 9^99 inside IFERROR?
9^99 creates a very large number. When a delimiter is not found, IFERROR returns that large number so it does not affect MIN; the earliest real position among the delimiters that do exist is always selected.
Should I subtract 1 or 2 in the LEFT formula?
For a two-character delimiter, subtract 1 to return all characters before the delimiter start position. If your data includes an extra character such as a space immediately before the delimiter, subtract 2 or wrap the result in TRIM to remove that space.
Can I use FIND instead of SEARCH?
Yes. Use FIND if delimiter matching must be case-sensitive and SEARCH if it should be case-insensitive. Replace each SEARCH function with FIND while keeping the same IFERROR and MIN structure.
What if my delimiters have different lengths?
The prefix formula still subtracts 1 from the earliest start position. For the remaining text, use MID with the matched delimiter length or add a helper calculation that identifies which delimiter matched first so the correct offset is used.
Does this formula work in WPS Office Excel?
Yes. WPS Office Excel supports SEARCH, IFERROR, MIN, LEFT, RIGHT, and LEN, so the same workbook and formulas work in both WPS Office and Microsoft Excel.




