Forum Discussion
Find And Replace to Add Line Breaks within Cells
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.
I don't know about other platforms, but Excel for Windows does recognize Ctrl+J in the Find/Replace dialog. You should see the insertion point (the blinking vertical bar) disappear except for one pixel.
Thank you yes, the killer is the different behaviour of Word which actually displays ^l (L) for line feed. This was compounded by Excel not recognising a tilde ~ which was the character I was trying to replace. Exactly why MS cannot get two of the key products to behave in the same way is rather strange.
That was the suggestion in the first reply. The OP never responded.
