How to Replace Line Breaks in Excel Cells on Mac with SUBSTITUTE
Question details
The user needs to remove or replace embedded line breaks in Excel for Mac cells because Find and Replace does not reliably locate line-feed characters.
- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- The user is cleaning a large spreadsheet where cells contain embedded line breaks and wants to replace them with spaces or other text without editing each cell manually.
- Observed behavior
- Excel for Mac's Find and Replace does not reliably find line-feed characters such as CHAR(10), so the standard Ctrl+J method fails or leaves line breaks in cells.
Duplicate the worksheet or create a backup, and identify the column that contains embedded line breaks. Confirm whether the breaks are line feeds by testing =SUBSTITUTE(A1,CHAR(10)," ") on one cell before filling the formula down.
Use a helper column and SUBSTITUTE to replace line breaks
Use this method when Find and Replace cannot locate line breaks in Excel for Mac and you need a reliable formula-based cleanup.
Excel for Mac may not interpret Ctrl+J as a line-feed character in Find and Replace. A helper column with SUBSTITUTE and CHAR(10) targets the embedded line-feed characters directly and works across large spreadsheets.
Click the column letter to the right of the affected data, then go to Home > Insert > Insert Sheet Columns. Alternatively, right-click the column and choose Insert.
In the first helper cell next to your data, type =SUBSTITUTE(A1,CHAR(10)," ") and press Enter. Adjust A1 to match the first original cell.
Select the helper cell and drag the fill handle down the column, or double-click the fill handle to apply the formula to the entire data range.
Select the helper column results, press Command+C, select the original cells, then go to Home > Paste > Paste Special > Values to overwrite the original line-break text.
After verifying the results, right-click the helper column and choose Delete to remove the formula column.
Use nested SUBSTITUTE for CHAR(10) and CHAR(13) line breaks
Use this variation if the first formula still leaves some line breaks because the file contains carriage returns instead of line feeds.
Replace Line Breaks in WPS Spreadsheets on Mac
Open your Excel workbook in WPS Office and use the same SUBSTITUTE and CHAR formulas to replace embedded line breaks. WPS Spreadsheets is a free, lightweight alternative that preserves .xlsx compatibility and offers a familiar spreadsheet layout on macOS.
- 1. Install WPS Office on Mac: Download WPS Office from the official WPS site, open the .dmg file, and drag WPS Office to the Applications folder.
- 2. Open the Excel workbook: Launch WPS Spreadsheets, click Open, and select the Excel .xlsx or .xlsm file that contains embedded line breaks.
- 3. Create the helper formula: Insert a helper column and enter =SUBSTITUTE(A2,CHAR(10)," ") in the first helper cell, replacing A2 with the first data cell.
- 4. Fill down and paste values: Fill the formula down the range, copy the helper results, select the original cells, and use Paste Special > Values to overwrite the line breaks.
- 5. Save the cleaned workbook: Delete the helper column, then save the file in Excel format to keep working with Microsoft Office users.
Frequently Asked Questions
Why doesn't Ctrl+J work in Excel for Mac to find line breaks?
Excel for Mac's Find and Replace does not reliably interpret Ctrl+J as a line-feed character, so using a helper column with =SUBSTITUTE(A1,CHAR(10)," ") is the dependable workaround.
What should I do if CHAR(10) does not remove all line breaks?
Try CHAR(13) or nest both break types in one formula: =SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(13)," ").
Can I replace line breaks with a comma instead of a space?
Yes, change the formula to =SUBSTITUTE(A1,CHAR(10),",") or use any replacement text between the double quotes.
Will this helper-column method work in WPS Office on Mac?
Yes, WPS Spreadsheets supports SUBSTITUTE and CHAR, so you can open the workbook in WPS Office and use the same formulas.
How do I paste the cleaned values back over the original data?
Copy the helper column results, select the original cells, and choose Paste Special > Values to overwrite the embedded line breaks without keeping formulas.
