Forum Discussion

DennisGalon's avatar
DennisGalon
Copper Contributor
Apr 05, 2024

Find And Replace to Add Line Breaks within Cells

Within a Cell Alt + Enter introduces an invisible Line Break or New Line Code. Believe that code is Char(10)   How do I use Find & Replace to Find characters (let's say $$) and replace this with t...
excel
GraceAA's avatar
GraceAA
Copper Contributor
Feb 26, 2025

In case anyone else stumbles on this question, this is the answer for an in-cell line break

Ctrl+H to bring up the Replace popup

Find What = ,

Replace With = here press "Ctrl+J"

Press Replace

CaptGreytower's avatar
CaptGreytower
Copper Contributor
Oct 30, 2025

Ctrl-J doesn't always work.   Found this method in an old thread that works very well:

Use CHAR(10) for a line break though.

The simplest way (though with a large spreadsheet, it may still not be simple) is to insert a blank column next to cells containing line breaks. In that column (say, column B), enter

B1: =SUBSTITUTE(A1,CHAR(13),"")

or

B1: =SUBSTITUTE(A1,CHAR(13)," ")

depending on whether you want to keep a space between the end of one line and the beginning of another.

Copy down as far as required. Select the column. Copy it. Select the original column. Choose Edit/Paste Special, selecting the Values radio button.

Delete the column you inserted.

https://learn.microsoft.com/en-us/answers/questions/4770312/how-can-i-find-replace-line-breaks-in-mac-2008-exc?forum=msoffice-all&referrer=answers

Resources