Split Multiline Address Cells into Separate Columns in WPS Spreadsheets
Question details
The user needs to split text stored on multiple lines within one spreadsheet cell into separate cells across the same row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Multiline address or similar records contain line-feed characters within a single cell and need to be separated into columns.
- Observed behavior
- Each value is displayed on a separate line inside one cell instead of in separate columns.
Confirm that the text contains actual line breaks rather than manually typed separators. Leave enough empty columns to the right of the source data so the split values do not overwrite existing cells.
Use Text to Columns with a line-feed delimiter
Use this method when each line inside a cell should become a separate value in adjacent columns.
Text to Columns can split selected cells at a custom delimiter. A line break is commonly stored as the line-feed character, CHAR(10).
In an empty cell, enter =SUBSTITUTE(A2,CHAR(10),";") and replace A2 with the source cell. If semicolons appear between the lines, the cell uses line-feed characters.
Select the column or cell range containing the multiline text, then open the Data tab and choose Text to Columns.
In the Text to Columns wizard, select Delimited, continue to the delimiter options, select Other, and press Ctrl+J in the Other delimiter box to enter the line-feed character.
Preview the split result, select a destination cell if needed, and click Finish to place each former line in a separate column.
Identify an unexpected separator before splitting
Use this method if Ctrl+J does not split the text, which can indicate that the cells use a different hidden character or separator.
Split Line-Break Text into Columns with WPS Spreadsheets
WPS Spreadsheets lets you identify line-feed characters with formulas and separate multiline cell content through the Text to Columns tool. It provides a familiar spreadsheet workflow for cleaning imported addresses, lists, and records.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the .xlsx, .xls, or supported workbook containing the multiline cells.
- 2. Test the line-break delimiter: Enter =SUBSTITUTE(A2,CHAR(10),";") in an unused cell to verify that the source content uses line-feed characters.
- 3. Start Text to Columns: Select the source range, open the Data tab, and click Text to Columns.
- 4. Enter the line-feed delimiter: Choose Delimited, select Other in the delimiter options, and press Ctrl+J in the delimiter field.
- 5. Finish the split: Review the preview, select the output location, and click Finish to distribute each line across columns.
Frequently Asked Questions
Why does Text to Columns not recognize the line breaks in my cells?
The cells may use a different hidden character than a standard line feed. Test the text with =SUBSTITUTE(A2,CHAR(10),";") or inspect a character with =CODE(MID(A2,position,1)) before choosing a delimiter.
How do I enter a line break as the Other delimiter in Text to Columns?
After selecting Data > Text to Columns and choosing Other as the delimiter, click the Other field and press Ctrl+J to enter the line-feed character.
Will splitting multiline cells overwrite nearby spreadsheet data?
It can if columns to the right contain values. Before clicking Finish in Data > Text to Columns, clear or move adjacent data, or set an empty destination range in the wizard.
Can I split multiline text into rows instead of columns?
Text to Columns splits values horizontally into columns. To place each line in rows, first split the text into columns, then copy the result and use Paste Special > Transpose, or use a suitable formula-based workflow.
