How to Convert Comma-Separated Values to Line Breaks in WPS Excel Cells
Question details
The user wants to replace comma-separated items with line breaks inside a WPS Excel cell so each item appears on its own line while remaining in the same cell.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Editing spreadsheet cells that contain comma-separated lists and converting them into vertically stacked text within each cell.
- Observed behavior
- The cell currently shows comma-separated items on one line; after conversion, the text should display as separate lines in the same cell.
Select the target range and confirm whether your comma-separated items include a space after each comma. This affects whether you enter a comma plus a space or only a comma in Find what.
Use Find and Replace to Turn Commas into Line Breaks in WPS Excel
Best for quickly converting all comma-separated entries in a selected range without using formulas.
Open your worksheet in WPS Office Excel and select the range that contains the comma-separated values you want to convert.
Press Ctrl+H to open the Find and Replace dialog box, and make sure the Replace tab is selected.
In the Find what box, type a comma followed by a space if your items use that delimiter, such as a comma and a space.
Click in the Replace with box, then press Ctrl+J to insert a line-feed character. The box may look empty, but the line break is entered.
Click Replace All, then enable Wrap Text from the Home tab and autofit the row height to display each item on its own line.
Use SUBSTITUTE and CHAR(10) to Create Line Breaks with a Formula
Best when you need a reusable formula or want to keep the original comma-separated data intact.
Convert Commas to Line Breaks in WPS Excel Cells
Use WPS Office Excel's Find and Replace or SUBSTITUTE formula to replace commas with line breaks, then enable Wrap Text and autofit rows for a clean vertical list in each cell.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook containing the comma-separated cells.
- 2. Select the target range: Highlight the cells whose delimiters you want to convert into line breaks.
- 3. Replace commas with line breaks: Press Ctrl+H, enter a comma and space in Find what, click in Replace with, press Ctrl+J, and choose Replace All.
- 4. Enable Wrap Text and autofit: On the Home tab, click Wrap Text and then autofit the row height to display each item on a separate line.
Frequently Asked Questions
Why does the cell still show everything on one line after Replace All?
The line break is inserted, but Wrap Text must be enabled. Select the cells, click Home > Wrap Text, and autofit the row height.
Can I convert comma-separated values for an entire column at once?
Yes. Select the entire column or the specific range, then use Ctrl+H and Replace All. All matching commas in the selected area are replaced with line breaks.
What if my comma-separated values do not include a space after the comma?
In Find and Replace, enter only a comma in Find what, or use the formula =SUBSTITUTE(A1,",",CHAR(10)).
How do I type a line break in the Replace with box?
Click in the Replace with box and press Ctrl+J. The box may appear blank, but Excel inserts a line-feed character.
Can I undo the conversion if I make a mistake?
Press Ctrl+Z immediately after Replace All to undo the change, or work on a copy of the sheet for a safer rollback.
