How to Replace Line Breaks in WPS Spreadsheet Cells Imported from CSV
Question details
The user needs to replace or remove hidden line break characters—carriage returns and line feeds—from spreadsheet cells after a CSV import.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A CSV file has been imported into WPS Spreadsheets, and cells contain embedded line breaks that split text within a cell or interfere with data cleanup.
- Observed behavior
- Ctrl+J works in some cells but not others because the CSV may contain CHAR(13), CHAR(10), or both.
Open the affected worksheet and select or identify the column that contains the imported CSV text. If you plan to use Find and Replace, copy an actual line-break character from a cell first so you can paste the exact character into the Find what box.
Use a Nested SUBSTITUTE Formula to Replace Both Carriage Returns and Line Feeds
Recommended when you want a consistent, repeatable cleanup in a helper column without missing either type of line break.
The nested SUBSTITUTE formula processes CHAR(13) carriage returns and CHAR(10) line feeds in one pass. This avoids the common issue of Ctrl+J matching only one line-break type.
Click a blank cell next to the first data cell, such as B1, and type =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)," "),CHAR(10)," ").
Press Enter, then drag the fill handle down the column to clean every imported cell.
If you need stable text, select the helper column, press Ctrl+C, right-click, and choose Paste Special > Values to replace the formulas with cleaned values.
Clean the Imported Column with Power Query Before Loading It
Use this when you are importing the CSV file and want to clean the entire column as part of the data import process.
Use Find and Replace with a Copied Line Break or Ctrl+J for Targeted Cleanup
Use this for quick, in-place cleanup when you know the affected range and can identify the line-break character.
Clean CSV Line Breaks Directly in WPS Spreadsheets
WPS Spreadsheets can open the CSV file, apply a nested SUBSTITUTE formula or Find and Replace, and remove hidden carriage return and line feed characters without leaving your current workflow.
- 1. Download WPS Office: Go to wps.com and download WPS Office, then install and open WPS Spreadsheets.
- 2. Open the CSV file: Click Menu > Open or drag the CSV file into WPS Spreadsheets to load the imported data.
- 3. Apply the line-break cleanup formula: In a blank helper cell, enter =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)," "),CHAR(10)," ") and fill it down the affected column.
- 4. Save the cleaned workbook: Save the sheet as a .xlsx file or export it as .csv, depending on where you need to use the cleaned data.
Frequently Asked Questions
Why does Ctrl+J work in some cells but not others after a CSV import?
CSV files can contain carriage returns, line feeds, or both. Ctrl+J inserts only a line feed, so cells that contain CHAR(13) carriage returns are not matched. Use the nested SUBSTITUTE formula or copy the actual carriage return into Find and Replace.
How can I remove line breaks instead of replacing them with spaces?
Use the same nested SUBSTITUTE formula but replace the space with an empty string: =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(10),"").
Does this method work for line breaks in .xlsx files as well?
Yes. Line break characters are the same in CSV and .xlsx files, so the SUBSTITUTE formula, Power Query, and Find and Replace methods all work.
How do I copy an actual line break character from a cell?
Double-click the cell or click in the formula bar, select only the line break between text lines, press Ctrl+C, then click in the Find what box and press Ctrl+V.
Can WPS Spreadsheets import and clean CSV files without Microsoft Excel?
Yes. WPS Spreadsheets opens CSV files directly, supports SUBSTITUTE and CHAR functions, and saves back to .xlsx or .csv format.
