Forum Discussion
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 the Line Break code???
17 Replies
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
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=answersYou are a hero!! I've been looking for this for 15 years! :)
Excel does not recognise "Ctrl J" in the find/replace dialog box, in Word you can enter "special characters" there is no option for this in Excel
Try Shift + Ctrl + J
sometimes Ctrl-J works, for some you may need to use the Shift key also. It worked with the Shift key for me; didn't try just Ctrl-J, I found the (better) Ctrl + Shift + J answer elsewhere.
^10 should also work.
If neither of those work, you can always try Alt+0010 on the numeric keypad (note that is four digits, not three).
- If Ctrl + J does not work, press the Alt key, and type 0010 on the number keypad (do not use the numbers above the letters)
^13 or Alt-0013 will work for the Carriage Return ASCII 13 character.
DennisGalon Do you happen to know the Ctrl+J equivalent for Excel for Mac, version 16.88
- I'm not familiar with Excel for Mac shortcut keys, but how about trying this? In a spare cell, place the formula =CHAR(10) and then copy the cell, and then immediately paste that into the same cell using a Paste Values. Then you could recopy the cell and use it in the Find and Replace dialog.
- Almost worked. Instead of a line break, it entered " "
I also tried =CHAR(13) as I saw that some versions of Excel for Mac used that insted of =CHAR(10).
Thank you.
Enter $$ in the 'Find what' box.
In the 'Replace with' box, press Ctrl+J. This enters a line break.
- Do you happen to know the Ctrl+J equivalent for Excel for Mac, version 16.88
I have Excel for Mac. Found this answer is an old thread. It worked.:
But use CHAR(10) if you want a line break:
ie. B1: =SUBSTITUTE(A1,"$$",CHAR(10)).
will replace $$ with a line break in the new columnThe 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
