How to Split Excel Address Data and Create City-Based Pie Charts
Question details
The user needs to separate a single column of combined dataset strings (containing date, address, postal code, and referral source) into distinct columns and visualize the data by city using pie charts.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a messy, combined dataset into structured columns for accurate analysis and charting.
- Observed behavior
- The data is currently lumped into a single column, preventing accurate summarization, filtering, and the creation of individual PivotCharts.
Ensure you have inserted enough blank columns to the right of your original data before splitting it to prevent the newly separated data from overwriting existing information.
Use Text to Columns and PivotCharts
Use the Text to Columns feature with specific delimiters to separate the combined data, followed by PivotTables to summarize and visualize the results.
The Text to Columns wizard is the most efficient native tool in Excel to parse strings into separate fields based on delimiters like commas or parentheses.
Select column A containing your combined data. Navigate to 'Data > Text to Columns'. Choose 'Delimited', select 'Comma' as your delimiter, and set the quotation mark as the text qualifier. This will split the data into date, address, postal code, and reason columns.
Insert an empty column after the newly created address column. Select the address column and click 'Data > Text to Columns' again. This time, choose an opening parenthesis '(' as the delimiter to separate the street from the city.
Select the city column, press 'Ctrl + H' to open Find and Replace. Enter a closing parenthesis ')' in the 'Find what' field, leave 'Replace with' blank, and click 'Replace All'. Add appropriate headers to all columns.
Select your entire cleaned dataset. Go to 'Insert > PivotChart'. Drag the 'City' field to the Axis (Categories) area and the 'Reason' (referral source) to the Values area. Change the chart type to a Pie Chart from the Design tab.
Easily Split Data and Create Charts in WPS Office
WPS Spreadsheet offers intuitive Text to Columns capabilities and powerful PivotChart features to quickly clean messy datasets and visualize them in just a few clicks.
- 1. Open your dataset: Launch WPS Spreadsheet and select the column containing your combined data strings.
- 2. Apply Text to Columns: Navigate to the 'Data' tab and click 'Text to Columns' to apply your specific delimiters like commas or parentheses.
- 3. Visualize with PivotCharts: Use 'Insert > PivotTable' or 'Insert > Chart' to summarize the separated city data into beautiful pie charts.
Frequently Asked Questions
Why is the Text to Columns feature grayed out?
This usually happens if you are currently editing a cell, have multiple columns selected at once, or the worksheet is protected. Ensure you only select a single column of data and verify that sheet protection is disabled under the Review tab.
Can I split text to columns using multiple different delimiters at once?
Yes, in the Delimited step of the Text to Columns wizard, you can check multiple delimiter options simultaneously (such as a comma and a custom character) to split the data in a single pass.
How do I change my PivotChart into a Pie Chart?
Click on your existing PivotChart to activate it, go to the 'Design' tab on the ribbon, select 'Change Chart Type', and choose 'Pie' from the list of available chart options.
Is there a faster way to extract data without Text to Columns?
Yes, you can use the Flash Fill feature. Type the desired extracted text (like the city name) in the adjacent column next to your data, press 'Enter', and then press 'Ctrl + E'. Excel will automatically detect the pattern and fill the rest of the column.
