How to remove line breaks and carriage returns in cells in Excel

Want to remove line breaks and carriage returns from a cell in your Excel file without having to do it manually? The good news is, you can use the “find and replace” function on Excel to get this job done.

Let’s say, we have the following example:

Initial dataset

In this case, the author has used the shortcut Alt+Enter to put the last name on a new line.

But now we need it together on a single line, with a space between the first name and last name instead of a line break.

Instead of:
Bastien
Mensink

we need:
Bastien Mensink, etc. etc.

If you want to replace these line breaks with a space, you can do this with Excel’s “find and replace” command.

  1. Press CTRL-F to launch the “find and replace” window.
  2. In the “Find what:” field, enter the line break code, which is character number 10. To do this, press and hold the Alt key, and then enter the numbers “010″ using the numeric keypad on the right-hand side of your keyboard (the numbers above the letters on your keyboard won’t work). You’ll then see a small dot blinking when your cursor is still in the field. If you have data imported from another source, sometimes the line breaks are also represented by character 13 instead of 10. If “010″ does not work, try “013″.
  3. In the “Replace with:” dialog, you can enter a space to replace the line breaks with spaces. You can also leave that blank to completely remove the line breaks without replacing them with anything.
  4. Press “Replace All”.

Excel's Find and Replace dialogue

*Samples and screen shots were derived from ASAP Utilities.

This entry was posted in Functions. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

5 Comments

  1. Gagan Jain
    Posted October 14, 2013 at 6:45 am | Permalink

    Hi, Its a very wonderful information which you have shared. I have a query if we want to this from laptop then how I can do?

    • karen
      Posted October 17, 2013 at 1:32 pm | Permalink

      Hi Gagan, you can certainly use the same steps above on a laptop!

    • John
      Posted November 19, 2013 at 6:49 pm | Permalink

      On a laptop, you may have to hold down the Function key (fn) and the Alt key, then use the “number pad”, which would be keys 7,8,9,U,I,O,J,K,L,M or however your keyboard is labeled.

  2. Dominique
    Posted April 22, 2013 at 8:35 am | Permalink

    Thank you very much, excellent and precise explanation.
    It works fine and helps me a lot to handle spread sheets output from a CAD program.

    Dominique

    • karen
      Posted April 22, 2013 at 12:53 pm | Permalink

      Dominique, happy to hear that the article helped you :)

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>