Data Import & Export

How to Separate City and Country Using Excel Text to Columns

Olivia Miller Oct 1, 2026 868 views

Question details

The user needs to split combined location data from a single column so that the city remains in column B and the country moves to column C.

How to Separate City and Country Using Excel Text to Columns

Product: Excel
Device & OS: not provided
Scenario: Organizing imported or manually entered location data where city and country names are combined in one cell.
Observed behavior: Combined location strings need to be parsed and separated into distinct columns for proper formatting and data analysis.

Before you start

Ensure the adjacent column (Column C) is completely empty to prevent overwriting any existing data when the split process executes.

Solution 1Recommended

Use the Text to Columns Wizard with Delimiters

This is the most efficient way to split combined location data using a specific delimiter, such as a space or comma.

The Text to Columns wizard allows you to define exactly where Excel should split your cell contents. For data formatted as 'City Country' or 'City, Country', defining the correct delimiter ensures accurate separation.

Prepare empty columns

Make sure column C is empty. If it contains data, right-click the column C header and select 'Insert' to add a new blank column.

Select the target data

Highlight all the cells in column B that contain the combined city and country values.

Open Text to Columns

Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns' in the Data Tools group.

Choose Delimited

In the wizard popup, select 'Delimited' as the file type that best describes your data, then click 'Next'.

Select the delimiter and finish

Check the box for 'Space' (or 'Comma' if your data uses commas). Check the Data preview to ensure the split is correct, then click 'Finish'.

Use the Text to Columns Wizard with Delimiters

Handling spaces in names: If your city or country names contain multiple words (e.g., 'New York' or 'United Kingdom'), using space as a delimiter will split them incorrectly into multiple columns. In such cases, replace spaces with commas first or use the fixed-width option if the character count is consistent.

Efficient Data Management with WPS

Easily Split Text to Columns in WPS Spreadsheet

WPS Spreadsheet offers a highly intuitive Text to Columns feature, allowing you to quickly split complex strings like city and country names. It is lightweight, completely free, and fully compatible with all Microsoft Excel workflows.

1. Select Data: Open your workbook in WPS Spreadsheet and highlight the column containing the location data.
2. Navigate to Data Tools: Click the 'Data' tab located on the top ribbon and select 'Text to Columns'.
3. Apply Delimiters: Choose 'Delimited', select your separating character (like a comma or space), and click 'Finish' to instantly split the text.

Flawless compatibility with Microsoft Excel (.xlsx, .xls, .csv) formats.Intuitive Text to Columns wizard for fast and accurate data separation.Lightweight, fast-loading, and free alternative for comprehensive data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

What if my city names have spaces, like 'Los Angeles'?

If you use a space delimiter, 'Los Angeles' will be split into two separate columns. To prevent this, ensure your data is formatted with a unique separator like a comma (e.g., 'Los Angeles, USA') and select 'Comma' instead of 'Space' in the Text to Columns wizard.

Can I use formulas instead of Text to Columns?

Yes, you can use text extraction formulas such as LEFT, RIGHT, FIND, and LEN to isolate text based on the position of a delimiter. Formulas are dynamic, meaning if the original text in column B changes, the separated results will update automatically.

Why did Text to Columns overwrite my adjacent data?

By default, the Text to Columns feature outputs the separated data into the columns directly to the right of your selected column. You must always insert blank columns next to your data before starting the wizard to prevent overwriting existing information.

Excel & Data Problems