How to Split Cell Contents into Adjacent Columns in WPS Spreadsheets
Question details
The user needs to separate combined city and state values from a single column into adjacent columns in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains location data such as "City, State" in one column, and the user wants the city and state in separate columns for reporting, sorting, or analysis.
- Observed behavior
- City and state values are combined in one column and need to be split using a consistent delimiter or text position.
Check whether the source text uses a consistent separator or fixed format, and insert an empty column to the right of the source data if you want to keep the original values.
Split Values with Text to Columns
Use this when your city and state values are separated by a consistent delimiter such as a comma, space, or hyphen.
Text to Columns is the fastest way to split an entire column without formulas. Choose Delimited if the separator is consistent, or Fixed width if the state always starts at the same position.
Select the column that contains the combined city and state values in WPS Spreadsheets.
Go to the Data tab and click Text to Columns.
Select Delimited, click Next, check the delimiter that matches your data such as Comma or Space, then choose an empty adjacent column as the destination and click Finish.
Use RIGHT, LEFT, FIND, and LEN Formulas for Fixed Formats
Use this when the state is always a two-character code at the end or when the city is followed by a comma and a space.
Use TEXTSPLIT for Dynamic Splitting in Supported Versions
Use this if your WPS Spreadsheets version supports TEXTSPLIT and you want a single dynamic array formula.
Split Excel Cell Contents into Adjacent Columns with WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use the built-in Text to Columns tool or compatible text formulas to split city and state data into separate columns while preserving your Excel file format.
- 1. Open your workbook: Launch WPS Office, open the Excel file containing combined city and state data, and select the source column.
- 2. Split the data: Click the Data tab, select Text to Columns, choose Delimited or Fixed width, and follow the wizard to place the split values into adjacent columns.
- 3. Verify the result: Check the adjacent columns to confirm the city and state values are separated correctly, then save the workbook in WPS Spreadsheets.
Frequently Asked Questions
Can I split cell contents without losing the original column in WPS Spreadsheets?
Yes. Insert an empty adjacent column before using Data > Text to Columns, and choose the empty column as the destination. The original data remains unchanged.
What should I do if my city and state data does not use a comma separator?
Use Data > Text to Columns, select Delimited, and check the actual delimiter such as space, semicolon, tab, or hyphen. You can also use formulas based on FIND or TEXTSPLIT for dynamic splitting.
How do I split a state code that is always at the end of the text?
Use the formula =RIGHT(A2,2) in an empty adjacent cell if the state abbreviation is always two characters. Drag the fill handle down to apply it to all rows.
Does WPS Spreadsheets support the TEXTSPLIT function?
TEXTSPLIT is supported in newer WPS Spreadsheets and Microsoft Excel versions. If it is not available, use Data > Text to Columns or the LEFT, RIGHT, FIND, and LEN formulas.
Can I split fixed-width values in WPS Spreadsheets?
Yes. Click Data > Text to Columns, choose Fixed width, and set the column break line where the city ends and the state begins.
