How to Extract Text from a URL Between Two Delimiters in WPS Office Spreadsheets
Question details
The user needs to extract a page name from a URL by returning the text after /web/en/ and before the file extension.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A URL is stored in a spreadsheet cell, and the user wants to isolate the page name segment between two delimiters.
- Observed behavior
- The goal is to return the page name, such as home-of-electric, from the full URL.
Confirm that the URL is in a single cell, such as A1, and identify the delimiter /web/en/ and the file extension. In current WPS Office versions, TEXTBEFORE and TEXTAFTER are available; older versions can use MID, FIND, and LEN.
Use TEXTBEFORE and TEXTAFTER to Extract Text Between Delimiters
Best for current WPS Office Spreadsheets versions because it extracts the page name in one short formula.
This method uses TEXTAFTER to locate the text after the sixth slash and TEXTBEFORE to remove the file extension.
Click a blank cell, such as B1, where the extracted page name should appear.
Type =TEXTBEFORE(TEXTAFTER(A1,"/",6),".") and press Enter.
Use MID, FIND, and LEN for Older Excel Versions
Use this alternative when TEXTBEFORE and TEXTAFTER are not available in your spreadsheet version.
Extract URL Text Between Delimiters in WPS Office Spreadsheets
WPS Office Spreadsheets gives you the same formula-based text extraction workflow as Excel. Use TEXTBEFORE and TEXTAFTER in current versions, or MID, FIND, and LEN for legacy compatibility, without converting files.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the URL cells.
- 2. Select the output cell: Click the blank cell where you want the extracted page name to appear.
- 3. Enter the extraction formula: Type =TEXTBEFORE(TEXTAFTER(A1,"/",6),".") and press Enter.
- 4. Apply the formula to other rows: Use the fill handle to copy the formula down if you have multiple URLs.
Frequently Asked Questions
Does WPS Office support TEXTBEFORE and TEXTAFTER?
Current versions of WPS Office Spreadsheets support TEXTBEFORE and TEXTAFTER. If these functions are not available, use the MID, FIND, and LEN formula instead.
What happens if the URL does not contain /web/en/?
The formula returns an error such as #VALUE!. You can adjust the delimiter in the formula or wrap it with IFERROR to show a custom message.
Can I extract a different segment from the URL?
Yes. For TEXTBEFORE and TEXTAFTER, change the delimiter or the instance number. For MID, FIND, and LEN, change the start text and end text arguments.
How do I apply this to many URLs?
After entering the formula in the first row, select the cell and drag the fill handle down to copy the formula to adjacent rows.
Are WPS Office Spreadsheets files compatible with Excel?
Yes. WPS Office Spreadsheets opens and saves .xlsx, .xls, and .csv files, so workbooks can move between WPS Office and Microsoft Excel.
