Return Multiple Continents for a List of Countries in Excel
Question details
The user needs to map a comma-separated string of country names located in a single cell to their respective continents, extract unique values, and join them back into a single comma-separated string.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning and categorizing geographical data where a single cell contains multiple countries, requiring a dynamic formula to look up matching continents and output a clean, deduplicated list.
- Observed behavior
- The user wants to transform an input string of multiple countries into an output string of unique continents using a single formula.
Ensure you have a master reference table mapping countries to continents, and verify that your spreadsheet software supports modern dynamic array functions like TEXTSPLIT, UNIQUE, and XLOOKUP.
Use TEXTSPLIT, XLOOKUP, UNIQUE, and TEXTJOIN
Combine modern dynamic array functions to split the text string, map the extracted countries against a reference table, remove duplicate continents, and recombine them.
This solution uses a nested formula approach. TEXTSPLIT breaks the list of countries into an array. XLOOKUP searches for each country in your reference table and returns the corresponding continent. UNIQUE ensures no continent is listed twice, and TEXTJOIN stitches the final array back into a readable text string.
Create a reference range with two columns: one named 'CountryList' containing all possible countries, and an adjacent column named 'ContinentList' with their respective continents.
Click on the cell where you want the resulting comma-separated continents to be displayed.
Type the formula: =TEXTJOIN(", ",TRUE,UNIQUE(XLOOKUP(TRIM(TEXTSPLIT(B2,",")),CountryList,ContinentList,""))) replacing B2 with the cell containing your comma-separated countries.
Press Enter. The formula will instantly split the country list, look up the respective continents, keep only unique values, and join them with a comma and space.
Process Multiple Rows Using BYROW and LAMBDA
If you have an entire column of comma-separated country lists, use the BYROW function to apply the lookup logic to all rows simultaneously without needing to drag the formula down.
Process Dynamic Array Formulas Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like XLOOKUP, TEXTSPLIT, and UNIQUE, allowing you to manipulate complex strings and map geographical data with ease and precision.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your geographical data.
- 2. Verify your mapping table: Ensure your country and continent reference table is correctly set up in a separate sheet or defined range.
- 3. Apply the formula: Paste the provided nested formula into your target cell to instantly map and deduplicate your comma-separated lists.
Frequently Asked Questions
Why does my formula return a #NAME? error?
This error occurs if you are using an older version of Excel that does not natively support modern dynamic array functions like TEXTSPLIT or XLOOKUP. Upgrading to a modern office suite like WPS Spreadsheet will resolve this compatibility issue.
What if the country names in my list have inconsistent spacing?
The TRIM function is included in the recommended formula specifically to clean up inconsistent spacing (like trailing or leading spaces) before the lookup happens. This ensures exact and accurate matches against your reference list.
Can I sort the resulting continents alphabetically?
Yes, you can achieve alphabetical sorting by wrapping the UNIQUE function inside a SORT function. Your formula segment would look like this: SORT(UNIQUE(XLOOKUP(...))) before passing the result to TEXTJOIN.
Is it possible to use VLOOKUP instead of XLOOKUP for this?
While VLOOKUP can be used, it is much harder to implement within dynamic array outputs and typically requires more complex workarounds. XLOOKUP is strongly recommended because it handles arrays natively and more efficiently.
