Split a Combined Name, Address, and City in Excel
Question details
Separate a combined name, street address, and city into individual spreadsheet columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A spreadsheet contains names, street addresses, and cities together in one column, and the user needs each part in its own column.
- Observed behavior
- The desired result is a separate name, street address, and city for each row.
Check a few rows to see whether the fields follow a consistent pattern or use a delimiter such as a comma. Leave enough empty columns beside the source data for the separated values.
Use Flash Fill to extract each field in Excel
Use Flash Fill when the combined entries follow a consistent pattern and you can provide an example of each desired field.
Create empty columns beside the combined data and label them Name, Street Address, and City.
In the first data row, type the name, street address, and city from the combined cell into their respective output columns.
Select the next cell under Name and choose Data > Flash Fill. Repeat in the Street Address and City columns. In Excel for Windows, you can also press Ctrl+E for each column.
Review the filled columns against the original entries, especially rows with unusual names or addresses. Correct any values Flash Fill interpreted incorrectly.

Split consistently delimited entries with Text to Columns
Use this method when the name, street address, and city are separated by the same delimiter in each row.
Split delimited names and addresses in WPS Spreadsheets
Use Text to Columns in WPS Spreadsheets to separate consistently delimited names, street addresses, and cities, then save the results in an Excel-compatible workbook.
- 1. Open the workbook: Open WPS Spreadsheets and choose File > Open to select your workbook.
- 2. Select the combined entries: Select the cells containing the names and addresses, and leave empty columns to the right for the results.
- 3. Split the text: Choose Data > Text to Columns, select Delimited, and choose the separator used between the fields. Check the preview before completing the split.
- 4. Review and save: Check the separated columns, then choose File > Save As and select XLSX if you need an Excel-compatible file.

Frequently Asked Questions
What if Flash Fill does not recognize the pattern?
Enter another correct example in the output column, select the next cell, and choose Data > Flash Fill again. Check the results because inconsistent source entries may still require manual corrections.
Can I split the data if commas separate the fields?
Yes. Select the combined cells, choose Data > Text to Columns, and select Delimited and Comma. Check the preview first, since commas within a field will also cause splits.
Will Flash Fill update if I change an address?
No. Flash Fill produces static results. Edit the output or select the output column and run Data > Flash Fill again after changing the source.
What can I do with inconsistently formatted entries?
Inspect and standardize the source text first, or use Data > From Table/Range to open Power Query and apply the transformations needed for your data.




