Convert a Google Sheets Filter and Split Formula to Excel in WPS Office
Question details
The user needs to recreate a Google Sheets formula in Excel or WPS Spreadsheets that filters out rows containing blacklist terms, applies PROPER to selected columns, and splits a comma-separated field into City, State, and Country, placing incomplete split values in the rightmost output columns.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is adapting a Google Sheets data-cleaning formula to an Excel-compatible workbook in WPS Office and needs the output columns to mirror the original behavior, including incomplete split values.
- Observed behavior
- The original Google Sheets formula filters out blacklist rows and splits column G into City, State, and Country, but the Excel-compatible version must also place incomplete split values in the rightmost output columns.
Before writing the formula, confirm which columns contain the blacklist terms (column E), the values to split (column G), and the columns to apply PROPER to (E, G, H, and M). Also check whether your Excel or WPS Spreadsheets version supports dynamic array functions such as FILTER.
Create a formula-based conversion with FILTER, PROPER, SEARCH, MID, LEFT, and IFERROR
Use this method when you need a direct Excel-compatible formula that matches the original Google Sheets logic and keeps incomplete split values in the rightmost columns.
This method keeps the transformation logic in the worksheet, so you can audit the blacklist rules and split results cell by cell.
If your WPS Spreadsheets or Excel version does not support dynamic arrays, apply the same functions in helper columns instead of using one spilled formula.
In WPS Spreadsheets or Excel 365, open the .xlsx workbook and click an empty area, such as P2:P4, to enter the blacklist terms that column E must not contain.
Click the first output cell, type =FILTER(A2:M100,(ISNUMBER(SEARCH(P2,E2:E100))+ISNUMBER(SEARCH(P3,E2:E100)))=0,"No matching records") and press Enter. Adjust the source range and blacklist cells to match the workbook.
In the output area, use =PROPER(INDEX(filtered_range,,5)) for column E, =PROPER(INDEX(filtered_range,,7)) for column G, =PROPER(INDEX(filtered_range,,8)) for column H, and =PROPER(INDEX(filtered_range,,13)) for column M, replacing filtered_range with the actual FILTER result range.
Click the City output cell and use LEFT, MID, FIND, and IFERROR to split the cleaned column G. Locate the first delimiter with FIND(", ",G2) and the second with FIND(", ",G2,FIND(", ",G2)+2), then wrap each result in PROPER.
Click the Country output cell first and enter a formula so a single value becomes Country; click the State output cell and enter a formula so a two-value split becomes City and Country or State with a blank in the missing position. Verify the City, State, and Country columns match the Google Sheets result.
Use Power Query to split column G and filter blacklist rows without formulas
Choose this option for a no-formula transformation in Microsoft Excel or when you want a repeatable cleanup workflow that is easy to update as the source range grows.
Convert and Clean the Spreadsheet Formula in WPS Office
WPS Spreadsheets can open your Excel workbook and apply Excel-compatible functions such as FILTER, PROPER, SEARCH, MID, LEFT, IFERROR, and ISNUMBER to reproduce the Google Sheets result. You can save the cleaned data as a standard .xlsx file and continue working in Excel or WPS Office.
- 1. Open the exported workbook in WPS Spreadsheets: Launch WPS Office, click Open, and choose the Google Sheets export or .xlsx workbook that contains the source data.
- 2. Add the blacklist criteria: Click an empty cell and enter the blacklist terms used by the FILTER and SEARCH check so the formula can reference them.
- 3. Build the filtered split formula: Select the first result cell, click the formula bar, and enter the FILTER, PROPER, SEARCH, MID, LEFT, IFERROR, and ISNUMBER formula, referencing columns E, G, H, and M.
- 4. Verify incomplete split values: Press Enter or Ctrl+Shift+Enter depending on your WPS version, then check that one-value and two-value split results appear in the rightmost output columns and that missing delimiters produce blanks.
- 5. Save as an Excel workbook: Click Menu > Save As and choose Excel Workbook (*.xlsx) to keep the file compatible with Excel and WPS Office.
Frequently Asked Questions
Does this formula work in WPS Spreadsheets or only in Excel 365?
It works in Excel 365 and in WPS Spreadsheets versions that support dynamic array functions. On older WPS versions, use helper columns with SEARCH, PROPER, LEFT, MID, RIGHT, and IFERROR to reproduce the same result.
How do I make incomplete split values appear in the rightmost City, State, and Country columns?
Wrap the delimiter lookups in IFERROR and map missing delimiters to blank or rightmost columns. If no comma-space is found, put the whole value in Country or State; if only one delimiter exists, put the first value in City and the remaining value in the rightmost available output column.
Can I use Power Query instead of a formula?
Yes. In Excel, click Data > From Table/Range, split column G by the comma-space delimiter, rename the split columns City, State, and Country, filter column E to remove blacklist rows, and then click Close & Load.
Which columns should PROPER be applied to?
Apply PROPER to columns E, G, H, and M as specified in the original Google Sheets logic. In Power Query, use Transform > Format > Capitalize Each Word to apply the same capitalization.
How do I exclude rows that contain any blacklist term?
Use FILTER with an ISNUMBER and SEARCH check for each blacklist term in column E, and keep rows where all blacklist checks return FALSE. In Power Query, use Text Filters > Does Not Contain for each term.
