How to Split Excel Data with Line Breaks and Hidden Delimiters in WPS Spreadsheets
Question details
The user needs to split a single Excel column into separate columns when the separator is an inconsistent mix of carriage returns, line feeds, and hidden characters between text and numbers.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Imported or pasted Excel data in one column uses inconsistent invisible delimiters, including carriage returns, line feeds, and possible hidden characters, between country names and numbers.
- Observed behavior
- The values remain in one column instead of being separated into adjacent columns, and the delimiter is not always visible in the cell.
Open the affected worksheet in WPS Spreadsheets and click a cell containing the data to inspect the delimiter in the Formula Bar. If you plan to use the macro method, enable the Developer tab or use Tools > Macro to open the Visual Basic Editor.
Split the column with Text to Columns using the hidden delimiter
Use this first when you can see the delimiter in the Formula Bar or can copy it from a cell; it is the fastest built-in option in WPS Spreadsheets.
Text to Columns splits a column at one delimiter at a time. If the data contains several different invisible characters, repeat the split once for each remaining character.
Click the column letter to select the whole column that contains the values to split.
Go to the Data tab and click Text to Columns.
Select Delimited and click Next. Check Other, click the Other box, and press Ctrl+J to insert a line break. If the values use another hidden character, copy that character from a cell and paste it into the Other box.
Click Next, set the Destination to the first cell where the split values should appear, and click Finish.
If some rows still contain unsplit values, select only those rows and repeat the operation with the next hidden character as the delimiter.
Use a WPS macro to split rows with mixed carriage return and line feed delimiters
Use this when different rows use different line break types, such as CRLF, CR, or LF, which Text to Columns cannot handle in a single pass.
Split Excel data with WPS Spreadsheets
WPS Spreadsheets opens your Excel workbook and gives you built-in Text to Columns plus macro support to split mixed line break and hidden delimiter data, while keeping .xlsx and .xls files compatible.
- 1. Download and install WPS Office: Download WPS Office from the official WPS site and install it on your computer.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Spreadsheets, click File > Open, and select the .xlsx or .xls file that contains the column to split.
- 3. Split the data column: Use Data > Text to Columns with Ctrl+J or the copied hidden delimiter, or run the supplied macro if rows contain mixed CR and LF delimiters.
- 4. Save the result in Excel format: Click File > Save As and choose Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
How do I enter a line break in the WPS Text to Columns delimiter box?
Go to Data > Text to Columns > Delimited, check Other, click the Other field, and press Ctrl+J. You can also copy the line break or hidden character directly from a cell and paste it into the field.
Why does Text to Columns leave some rows unsplit?
The rows may use a different invisible delimiter such as CR only, LF only, a tab, or a non-breaking space. Select the remaining rows, copy the actual hidden character from a cell, and run Text to Columns again with that delimiter.
Will the VBA macro overwrite my original data?
No, the example macro reads column A and writes the split values to columns B, C, and D, so the original column A remains unchanged.
Can I split text and numbers that are in the same cell without a delimiter?
If text and numbers are not separated by a line break or hidden delimiter, use WPS Spreadsheets' Flash Fill or formulas that locate the first digit and split the string into text and number parts.
