In a previous post, I showed you how to add a hard return within an Excel worksheet cell. You can check out that post here:
However, what if you have lots and lots of cells with hard returns and you want to replace these carriage returns with another character or just something else?
This may seem like a remote possibility, but you may get spreadsheets as an analyst where you import data that has hard returns and you want to find and replace this data before uploading it into another program.
Here is how you can do a find and replace on a hard return in an Excel spreadsheet:
2) Use these key strokes to enter the hard return/carriage return into Find What box:
Note that these are not your standard zero’s and one’s from the numbers you see above the letters on your keyboard. They must be entered from the “NUMBER PAD”.
Hold the ALT key down and then press 010 on the number pad. If that doesn’t work, you can also try by holding down the ALT key down and then press 013 on the number pad.
It is your curser that is spanning two different lines so it is blinking in and out so show you the highlight.
(note what you see above is a comma followed by a space and then the vertical line is a picture of the curser.)
Now press “Replace All” and your work is done.
Check out the video demonstration here to see it in action:
Let me know any little known Excel tips and tricks that you know in the comments below. Also, don’t forget to subscribe to the blog so you get the next Excel post right in your inbox.