Fix Imported Line Breaks in WPS Office Excel Without Editing Each Cell
Question details
The user needs to split web-imported address data that contains carriage return characters (character 13) instead of standard line-feed characters, so Text to Columns can process more than 100 rows at once.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user imported web-based address data into WPS Office Excel and needs to split it into separate columns for more than 100 rows at once.
- Observed behavior
- Imported cells contain carriage returns (character 13) where Excel expects line-feed characters, so Text to Columns does not recognize the intended line breaks and cannot split the data correctly.
Save a copy of the imported workbook and confirm that the Developer tab is enabled in WPS Office Excel so you can open the VBA editor.
Run a VBA Macro to Replace Carriage Returns and Apply Text to Columns
Use this solution when you need to clean and split more than 100 rows at once without editing each cell manually.
The imported carriage return is character 13, also represented as vbCr in VBA. The macro replaces that character with a temporary delimiter and then uses Text to Columns to split the address data in one pass.
Because the macro changes the selected cells in place, test it on a copy of the workbook first.
Open the imported workbook in WPS Office Excel, select File > Save As to create a copy, and enable macros if the security prompt appears.
Press Alt + F11 to open the Microsoft Visual Basic for Applications editor, then right-click the project and choose Insert > Module.
Copy the provided macro code into the new module, then press Ctrl + S to save the project.
Return to the worksheet and select the range of imported address data that contains the carriage return characters.
Press Alt + F8, select SplitData, and click Run. The macro replaces carriage returns with "|" and runs Text to Columns using the same delimiter.
Use Find and Replace to Manually Swap Carriage Returns for a Delimiter
Use this manual workaround for small datasets when you prefer not to run a macro.
Fix Imported Line Breaks in WPS Office Spreadsheets with a Macro
Use WPS Office Spreadsheets to run a VBA macro that replaces character 13 carriage returns with a temporary delimiter and splits address data into columns in one pass.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the imported .xlsx workbook that contains the carriage-return address data.
- 2. Enable macros and open VBA: If the macro security prompt appears, enable macros, then press Alt + F11 to open the VBA editor.
- 3. Insert and run the macro: Insert a new module, paste the SplitData macro, select the data range, and press Alt + F8 to run SplitData.
- 4. Review and save: Confirm the address data is split into the expected columns, then save the workbook in WPS Office format or as .xlsx.
Frequently Asked Questions
What is character 13 in imported Excel data?
Character 13 is a carriage return, often represented as vbCr in VBA. Web-imported data can use it instead of the line-feed character that Excel normally expects for standard line breaks.
Does this VBA macro work in WPS Office Excel?
Yes, WPS Office Spreadsheets supports VBA macros. Open the VBA editor with Alt + F11, insert a module, paste the macro, and run it from the macro dialog with Alt + F8.
Can I use a different temporary delimiter instead of "|"?
Yes. In both the macro and the manual Text to Columns method, you can replace "|" with another character, such as ";" or "^", as long as that character does not already appear in the data.
Why should I test the macro on a copy of the workbook first?
The macro changes the selected cells in place. Running it on a copy prevents accidental data loss if the delimiter appears elsewhere or the wrong range is selected.
