Find and Replace Line Breaks in Excel

Find and Replace Line Breaks in Excel

When you want to create a line break (line feed) in a cell, you press Alt + Enter, to start a new line. You can put one or more line breaks in a cell, to make the contents easier to read. But how can you find and replace line breaks in Excel?

Find Line Breaks in Excel

Line breaks are easy to add, but a little trickier to remove.

To find specific text in Excel, you can use Ctrl + F to open the Find and Replace dialog box. However, if you try to type Alt + Enter in the Find What box, you’ll just hear a beep from your computer. Excel won’t let you put that shortcut into the Find What box.

Instead of using Alt + Enter, you can use a special shortcut to enter a line break in the Find What box: Ctrl + J 

Why does that work? A line break is character 10 in the ASCII character set, and the Ctrl + J shortcut is the ASCII control code for character 10.

Find and Replace a Line Break

To find a line break, and replace it with a space character, follow these steps. There is a video below, that shows the steps.

  1. Select the cells that you want to search
  2. On the keyboard, press Ctrl + H to open the Find and Replace dialog box, with the Replace tab active
  3. Click in the Find What box
  4. On the keyboard, press Ctrl + J to enter the line break character — NOTE: No text will appear in the Find What box — just a small blinking dot
  5. Press the Tab key on the keyboard, to move to the Replace With box
  6. Type a space character
  7. Then, click Find Next or Find All, to find the cells with line breaks.
  8. OR, click Replace or Replace All, to replace the line breaks with space characters.

NOTE: If these steps don’t work, try the tips and troubleshooting suggestions below.

To get the Excel workbook, with the Add or Remove Line Breaks in a Cell in Excel example, go to the Excel Line Breaks page on my Contextures site.

Tips and Troubleshooting

Update: Here are a few tips and troubleshooting suggestions, to use while you find and replace line breaks in Excel. Thank you to everyone who commented, and added suggestions.

  • 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)
  • If the Excel file has never been saved, try saving the file before doing this find and replace
  • Try doing this find and replace when you first open Excel, before using the Find box for any other searches. Or, try the next tip — you might have extra line breaks in the Find box.
  • Be sure that you aren’t inserting more than one line break in the Find box — the Find box looks empty after you type Ctrl+J, so it is easy to accidentally type in there again. To avoid this problem:
    1. Click on the Replace tab — you’ll see a light border around the word.
    2. Press the Tab key — that will select the Find What box, and anything that is entered there.
    3. Type Ctrl+J (that will replace anything that you previously typed in Find)

Video: Find and Replace Line Breaks

Watch this short video, to see the steps for adding a line break in a cell. Then see how to find the line breaks in Excel, and replace them with space characters.

87 thoughts on “Find and Replace Line Breaks in Excel”

  1. Ctrl-J is an easy way to put a line break into a number format. Unfortunately out stored working in chart axis tick label dialogs in Excel 2010 or 2013.

    1. Doh! “out stored” = “it stopped”. Stupid swype. That comment should be:
      Ctrl-J is an easy way to put a line break into a number format. Unfortunately it stopped working in chart axis tick label dialogs in Excel 2010 or 2013.

  2. Regarding this statement…
    4. On the keyboard, press Ctrl + J to enter the line break
    character — NOTE: Nothing will appear in the Find What box

    Your “NOTE” is not entirely true… if you look carefully at the bottom left corner of the “Find what” field, you should see a small blinking “dot”… it is not a dot, rather, it is the top of the blinking text cursor on the line below (which Ctrl+J created) peeking through. If you press the left arrow, you will see the full text cursor because you have moved it to the other side of the Line Feed character placed by Ctrl+J… press the right arrow and the text cursor nearly disappears again as it move back to the other side of the Line Feed character again.

      1. Forgive me, Leonardo, but in order to avoid misinterpretation of your comment, I will take it upon myself to correctly translate a very common mistake by native Spanish speakers. People who speak Spanish very often think that “any” means “no” or “none”.
        The comment should read:
        You solved me a huge curiosity and NO page on the subject helped me more than u!
        By the way, I also want to thank you, Debra, for solving a long desired solution to this Alt+Enter dilemma, and Ctrl+J is good news to me too.
        Cheers!!

    1. I am working in Excel 2019 (Office 2019). See my work. It is not working. Please suggest.
      Entered
      One Alt + Enter Two Alt + Enter Three. It works. But after pressing Find and Replace, I am not able to replace Alt + Enter character using Ctrl + J and replacing by Space. It does not work. See the message “We could not find anything to replace” and “FYI: It’s Possible the data you are trying to replace is in a protected sheet. Excel can not replace data in protected sheets.

      It is a new sheet, it is not protected. I am not able to replace. PLease suggest

  3. When I encounter unknown or invisible ASCII characters embedded in strings, I try to copy them in the edit line, then paste them into “Find what” field, or even use them in a “SUBSTITUTE” formula.

      1. This condition is particularly important in my case. Great tip and should be incorporated in the post itself.
        Thank you for sharing.
        It has saved me lots of time!

  4. Hi Debra,
    The above mentioned trick is working only in one cell. Thereafter it it is not working for other cells of the same sheet.
    Please advise why it so?
    Regards
    Sonu

  5. Couldn’t make this work in excel 2007 at all. The blinking dot appeared but it said it couldn’t find anything to replace.

  6. Hi,
    We can also give ‘Alt+0010’ instead of line breaks in the find what textbox. Same like Ctrl + J
    Thanks,
    Damodar

  7. Great tip! I was getting inconsistant results using Alt+0010 to find and replace line breaks but Ctrl+J appears to be happily stable. Thanks for making this available.
    Fifi

  8. This tip doesn’t work on an unsaved document. The document should be saved and then Ctrl J works..

  9. Thanks for this,
    It’s amazing how much you can learn and can forget if you haven’t used a technique after a few years.

  10. Thanks – this has really quickly resolved a long-standing problem I’ve had of having to create extra columns to place ‘replace’ results into (TRIM etc). Much better 🙂

  11. NOTE: A variation of this can be used for INSERTING line breaks. I gather weekly status updates on team tasks that I want date stamped, so I pre-format the spreadsheet before team meeting:
    eg: Find/Replace all: 05/27/15 – with 06/03/15 – ^J05/27/15 –

  12. Does not work in my version of Excel 2010. I download a csv file from an Oracle table and convert it to Excel 2010. It contains a text field for which some have just the text and some the text and a carriage return or linefeed. I can clear those out using various, time consuming, methods such as copy and paste or =Clear(), but was hoping for a Find/Replace. I tried this as soon as I saw it. Sometimes I get the flashing dot when I type Ctrl+J, most times I don’t, and in neither case, does it find anything. Alt 0010 does not work either. Any ideas why it works for others but not for me? Am I possibly getting something besides a linefeed? Sure looks like one in the cell.
    Thanks,
    Biff

  13. I’ve tried CTRL+J and it didn’t work (Excel 2010). I tried ALT+010 and it worked once, but I can’t get it to work again. Any clues?

      1. I thought I had sorted this way back when, but I’m confused again.
        I start with e.g.
        The Farm
        The Bigfield
        Clapham
        Bedford
        after using find Ctrl+J and replace with a space I get this:
        The Farm
        The Bigfield
        Clapham
        Bedford
        Any ideas?

  14. This is supposed to be quasi-standard of office software, and there is no obvious way to find information about this and many other little extras in the official documentation … MS must be kidding!
    This is as annoying as the missing “export” option for document formats – why does MS Office always assume you want to “convert” and carry on editing in a different format, instead of just exporting to it ?? Mind-boggling!
    Anyway, thanks for the valuable tip!

  15. Hi Debra,
    Ctrl + J – what a great tip, thanks! Can’t believe I never knew (or forgot) this.
    Looking over some of the other comments, I noticed a few people were having trouble getting this to work. It was “on and off” for me too, until I realized that I was occasionally inserting more than one Ctrl J into the the Find box. Because it’s invisible, it’s easy to do. The Find/Replace dialog retains it’s characters during a session, so when you re-open it, the Ctrl J box looks empty, but it’s not. Once I realized this, it worked every time for me. Hopefully, this may be helpful to others having problems.
    Regards
    Dave

  16. I have searched high and low to do something similar, but slightly different… I would like a formula to remove everything to the right of a line break in a cell… I know a line break is Chr(10), but can’t figure out how to incorporate that into the “=replace” formula. Any help would be hugely appreciated!

    1. Sorry… I forgot to mention that once you insert the Ctrl+J character and the type the *, you will not see the asterisk either because it is below the viewing area of the Find field… you will only see the “blinking dot”. If you play with the arrow keys you will be able to follow the dot and know how many characters, if ever, you have typed after the Ctrl+J. That would be in case you want to replace or erase a specific string. Play with it… type several characters after the Ctrl+J and then move the cursor back and forth with the arrow keys.
      Cheers

  17. Hi DJS,
    Have you tried inserting Ctrl+J and an asterisk in the find field?
    When you insert a Ctrl+J in the Find field, you will notice a tiny blinking dot at the beginning of the field.
    That is the cursor that has gone to the “next line” as it does in Excel when we insert an Alt+Enter. If you press the Left arrow you will see the cursor fully reappear in the field and the Right arrow will make it go back down.
    Anyway, back to our scheduled program, simply insert a Ctrl+J followed by an *.
    I type capital J for clarity, but you actually type a lower-case j.
    I hope this helps.
    Cheers

    1. Hi Alphonse,
      I tried giving the asterisk afte CTRL+J.. But its replacing the entire second line with space.
      So my result is coming this way.
      Existing:
      Retired
      Policy
      Expected:
      Retired Policy
      Actual:
      Retired
      Any other tricks I can try.

    1. Hi Steve,
      I tried the formula and it just returned #VALUE! error.
      Did I miss something here?

  18. Any suggestions on doing the reverse? I need to replace a special character “|” with a line break. When I try this above mentioned method using the Replace All option, all the data in each cell is deleted where the “|” exists. Additional Note: I am selecting an entire column, but also tried it on an individual cell.

  19. Incredible – the video really helped !!! I have a massive table in word with tonnes of line break that I was struggling to get into excel. I replace all line breaks by a text – my name – and then in excel I replaced my name to crtl+J

  20. Hi,
    I tried, Capital J to replace the line break and the formula and all tips and tricks from this conversation.
    unfortunately, nothing turned out.
    I am trying to replace a line break with a space in a column of my .CSV sheet.
    I copied the column and paste into another blank sheet and saved it and tried again with all possible options. But couldn’t get the replacement. I can see that copy paste is the only option now to get my work done. But I don’t want to take a chance of losing the original data in replacing the cells when I filter them out during copy paste.
    Any help is greatly appreciated. Thank you!

  21. Before seeing “Ctrl+J” on this webpage, I was at a total loss.
    I had an MSWord table containing forced line returns within cells and when copying the info to Excel it was putting the “next line” info, not into the same cell but into a new cell.
    This (plus a couple of logical gyrations) will save me a couple of hours today and several hours in the future. . . PLUS it may appear that I know some “magic”. ; – )

  22. This is so frustrating! Sometime Ctrl+J works and sometimes it doesn’t. I need to do this once every three months and each time I think I’ve cracked it, but then it doesn’t work next time.

  23. Hi – I have line breaks (Ctrl+J) that I would like to replace with paragraph breaks (^p in Word).
    Do you know how to do this in Excel?
    I know you usually want to get rid of them in Excel, but I need them for my translation software to detect the end of a segment.
    (At the moment I can export to Word or change the segment definition in the translation software, but changing the source Excel file would be the neater solution). Many thanks
    Giles

  24. Is there a way to replace the line break with the tab button? Using the example in your video, I am looking to shift the “two”, “six”, and “seven” over to column C.

  25. Hello everyone,
    I tried to find with key word: “replace break line in excel”,
    and I see this site showing up in the first.
    It helps me enter the enter character on the search bar (CTRL + J or ALT + 010),
    But it does not help me as I would like.
    Actually, my line breaks are CRLF – not only LF,
    So just enter the enter is not enough.
    I also could not find a way to enter CR characters (0x000D – 013), so I added a question mark (?) before the enter character instead.
    —-
    For example:
    ______
    |Apple | => Apple\r\nSamsung
    |Samsung|
    ….

    if you want to replace into: Apple-Samsung
    you can search with keywords: Apple?\n
    (\n is the enter character = CTRL + J or ALT + 010)
    —-
    Hope to help someone like me.

  26. I had a situation where I had some cells with NLs in text, which I wanted, and a large number of cells with one or two spurious NLs that I wanted rid of. Using a Mac, and latest Excel, I couldn’t get any of the find-and-replace ideas to work. Just a lot of angry pings from Excel. Eventually I realised that a combination of CLEAN and a few other things did the job.

    1. Create sheet 2 to receive the manipulated data from sheet 1.
    2. In sheet 2, R1C1, enter the formula =IF(LEN(Sheet1!RC)<5,"%",Sheet1!RC) where "%" is any convenient character that does not appear in sheet 1. For me, this meant that all the cells with just one or two invisible NLs now appeared as % in sheet 2. At last I could see the buggers.
    3. Select all of sheet 2, copy, and paste special / values. So now sheet 2 only contains text, most of which is cells containing a single %.
    4. Keeping all of sheet 2 selected, Find-and-replace % with nothing at all.

    This did it for me. I realise that my circumstances are not the same as everyone else's but it may help. You could perhaps use SUBSTITUTE to replace NL with % or something like that.

    Ridiculous that Excel requires you to spend ages on simple problems like this.

  27. Great! thank you! Normally of course, if you paste from a doc table with CR or LF, it goes into the next cell down for each CR and I have too many rows per original table row.

    But take complicated text in a table in Word, replace all paragraphs and line feeds with text (I use “[para]” ) and I can paste the table into excel, it goes in one row each table row.

    Then I find and replace [para] with ctrl-j, and I have my formatted text within one cell, within one row!!!! 🙂 😀

    where I have too many lines I also replace ctrl-j ctrl-j with one ctrl-j.

  28. How do I replace CRLF instead? My cells are filled with CRLF characters.

    Also, what is the ALT+0101 ? They do not seem to be ASCII characters because ALT+0101 produces e, and that is ENQ in the standard ASCII list. It can’t be unicode because e would be U+0065. So what is this please?

Leave a Reply to Oswald Heger Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.