Return People’s Names by City From a Comma-Separated List in WPS Spreadsheets
Question details
Return the first and last names of people whose comma-separated city list contains a city selected on another worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A people list stores multiple cities in one cell, and the user wants a separate worksheet to display matching names for a selected city such as Windsor.
- Observed behavior
- The desired result is a dynamic list of full names for every row whose city-list cell contains the selected city.
Place the city to search for in a single input cell, such as A1 on the results worksheet. Check that city names in the source list use consistent comma-separated formatting and spelling.
Use FILTER and SEARCH to return matching full names
Use this method when you want a dynamic list that updates automatically after changing the selected city in the results sheet.
The formula combines the first-name and last-name columns, then filters those names when SEARCH finds the city entered in the selected input cell. Adjust the worksheet name and row ranges to match your workbook.
On the results worksheet, type the city name to search for in cell A1, for example, Windsor.
Click cell A2, or another empty cell where the returned list of full names should begin.
Enter =FILTER('People Sheet'!$A$2:$A$200&" "&'People Sheet'!$B$2:$B$200,ISNUMBER(SEARCH($A$1,'People Sheet'!$C$2:$C$200)),"") and press Enter. Replace People Sheet with the name of your source worksheet and adjust the ranges if needed.
Replace the value in A1 with another city. The spilled results beginning in A2 update to show the matching people.
Filter people by city with WPS Spreadsheets
WPS Spreadsheets can use FILTER, SEARCH, and text concatenation to create a live list of names from comma-separated city entries. Enter a selected city, add the formula once, and let the results update as the source data changes.
- 1. Open the people list in WPS Spreadsheets: Open the workbook and confirm that first names, last names, and comma-separated city lists are in separate columns.
- 2. Create a results worksheet: Click the plus button next to the sheet tabs to add a worksheet, then enter the city to find in cell A1.
- 3. Add the dynamic formula: Select cell A2 and enter a FILTER formula that joins the name columns and searches the city-list column for A1.
- 4. Review the returned names: Press Enter and keep the cells below A2 empty so WPS Spreadsheets can display every matching name.
Frequently Asked Questions
How do I return both first and last names in one FILTER result?
Join the name columns inside the FILTER array argument, such as 'People Sheet'!A2:A200&" "&'People Sheet'!B2:B200. This returns each matching person as one full-name value.
Why does my FILTER formula return a spill error?
A spill error means cells in the output area are occupied. Clear the cells below and beside the formula cell, then press Enter again.
Can I search for a different city without editing the formula?
Yes. Enter the city in the input cell referenced by the formula, such as A1. Changing A1 updates the FILTER results automatically.
What happens if no person has the selected city?
The final "" argument in the FILTER formula returns a blank result when no matches are found. You can replace it with text such as "No matches" if preferred.
Can SEARCH match part of a city name unintentionally?
Yes. SEARCH looks for text within the cell, so a short city name can match part of a longer name. Use consistent city names and comma-separated entries, or use a delimiter-based formula when exact matching is required.
