TEXTSPLIT on a comma and a space
A delimiter can be more than one character, so no stray spaces are left behind.
Task:
Locations were exported as "City, Country". In B2:B5 split each so the city and country land in B and C, with no leading space on the country.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of TEXTSPLIT
0/3The same formula in the other shapes it takes at work.
- Split a stuck-together name column
TEXTSPLIT breaks text at a delimiter into separate cells.
CoreIntermediate - TEXTSPLIT a code into its parts
One formula per row; each spills across as many columns as there are parts.
Intermediate - TEXTSPLIT on a comma and a space
A delimiter can be more than one character, so no stray spaces are left behind.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Location | City | Country |
| 2 | Lisbon, Portugal | ||
| 3 | Ghent, Belgium | ||
| 4 | Kraków, Poland | ||
| 5 | Porto, Portugal |
What this exercise teachesMay contain the answer
A stray leading space makes " Portugal" and "Portugal" different values, so lookups and counts quietly fail later. Splitting on the exact separator — comma and space — means there is nothing to TRIM afterwards.