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:
Excel Line Break/Hard Return within a Cell
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 my the sample data:
and you want to replace all the hard returns in cell A1 with a comma and a space, like this:
Here is how you can do a find and replace on a hard return in an Excel spreadsheet:
1) Press CTRL+E+E to bring up the Find and Replace dialog box
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.
After you do that you then you will see just a hint of a character that looks like a period blink in and out:
It is your curser that is spanning two different lines so it is blinking in and out so show you the highlight.
Now add in your text in the Replace With box. In our case, it is a comma followed by a space:
(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.
Steve=True
