In this tip you'll find 3 ways to remove carriage returns from Excel cells. You'll also learn how to replace line breaks with other symbols. All solutions work for Excel 365, 2021, 2019, and lower versions.
There can be different reasons for line breaks occurring in your text. Usually, carriage returns appear when you copy text from a webpage, get a workbook that already contains line breaks from a customer, or you add them yourself using Alt+Enter.
In any case, what you want to do now is delete carriage returns since they don't let you find a phrase and make column contents look disorganized when you turn on the wrap text option.
Please note that initially the terms "Carriage return" and "Line feed" were used in a typewriter and meant 2 different actions, you can find more at Wiki.
Computers and text processing software were created taking into consideration the typewriter specificities. That's why two different non-printable symbols are used now to indicate line break: "Carriage return" (CR, ASCII code 13) and "Line Feed" (LF, ASCII code 10). Windows uses 2 symbols one by one: CR+LF, and LF for *NIX systems. Be careful: in Excel you can find both variants. If you import data from a .txt or .csv file, you are more likely to find Carriage Return + Line Feed. When you break a line using Alt+Enter , Excel inserts Line Feed only.
In case you get .csv files from a person who uses Linux, Unix, etc., you'll find only Line Feeds again.
All these 3 ways are really quick. Feel free to pick the one that suites you best:
Tip. For your are looking for a solution to the opposite task, then read how to quickly add a line break in Excel cell.
Remove Carriage Returns manually
Pros: the fastest way.
Cons: no any additional features :(.
Please find the steps for eliminating line breaks using Find and Replace:
- Select all cells where you want to remove or replace carriage returns.
- Press Ctrl+H to open the Find & Replace dialog box.
- In the Find What field enter Ctrl+J. It will look empty, but you will see a tiny dot.
- In the Replace With field, enter any value to replace carriage returns. Usually, it is space to avoid 2 words join accidentally. If all you need is deleting the line breaks, leave the "Replace With" field empty.
- Press the Replace All button and enjoy the result!
Delete line breaks using Excel formulas
Pros: you can use a formula chain / nested formulas for complex cell text processing. For example, it is possible to remove carriage returns and then eliminate excess leading and trailing spaces and those between words.
Or you may need to delete carriage returns to use your text as an argument of another function without changing the original cells. For example, if you want to be able to use the result as an argument of the function =lookup ().
Cons: you'll need to create a helper column and follow many extra steps.
- Add the helper column to the end of your data. You can name it "1 line".
- In the first cell of the helper column (C2), enter the formula to remove / replace line breaks. Here you can see several helpful formulas for different occasions:
- Handle both Windows and UNIX carriage return/ line feeds combinations.
=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"") - The next formula will help you replace line break with any other symbol (comma+space). In this case lines will not join and extra spaces will not appear.
=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ") - If you want to remove all nonprintable characters from text, including line breaks:
=CLEAN(B2)
- Handle both Windows and UNIX carriage return/ line feeds combinations.
- Copy the formula across the other cells in the column.
- Optionally, you can replace the original column with the one where the line breaks were removed:
- Select all cells in column C and press Ctrl + C to copy the data to clipboard.
- Now pick the cell B2 and press the Shift + F10 shortcut. Then just press V.
- Remove the helper column.
VBA macro to get rid of line breaks
Pros: Being created once, can be reused in any workbook.
Cons: you need to have the basic knowledge of VBA.
The VBA macro from the example below deletes carriage returns from all cells in the currently opened worksheet (active worksheet).
If you don't know VBA really well, see How to insert and run VBA code in Excel
Remove carriage returns with Text Toolkit
If you are a lucky user of our Ultimate Suite for Excel, then you do not need to waste time on any of the above manipulations. All it takes is these 3 quick steps:
- Select one or more cells where you want to delete line breaks.
- On your Excel ribbon, go to the Ablebits Data tab > Text group, and click the Convert button.
- On the Convert Text pane, select the Convert line break to radio button, type the "replacement" character in the box, and click Convert.
In our example, we are replacing each line break with a space, so you put the mouse cursor in the box and press the Enter key:
As the result, you will have a neatly organized table with one-line addresses:
If you are curious to try this and 60 more time-saving tools for Excel, you are welcome to download a trial version of our Ultimate Suite. You will be amazed to discover a-few-clicks solutions for the most challenging and tedious tasks in Excel!
Video: How to remove line breaks in Excel
158 comments
Excellent!! Ran the formula - Amazed IT!:-)
Thanks so much for this! Quick and painless solution that worked the first time, which is never the case :-)
Thanks Alexander. Solution 1 worked for me.
Thanks a lot !!!
pedro louro method worked great! Thanks!
Excellent!
Your macro was excellent thanks!
thank you very much, it was useful for me
Excellent tips with easy to follow instructions...Thank you!
This was a great help. I used the Clean function to remove unprintable characters before creating a JSON file from the Excel file. Before doing this , I was cleaning the JSON file manually.. Thank You !
Great idea. I've been wondering how to do that for some time but never had a compelling reason (more than curiosity). I co-worker asked today if I could do it and had a huge need - the CNTL-H, CNTL-J, inserted a ^ worked great.
Very helpful! Thank you so much!
Incredibly helpful - thanks much
thanks a lot
at was successful
I tried to get the CR by pressing ALT+13, but did not work
I was having the same problem of replacing lots of CRs on a Excel Sheet
and by 'mistake' I found a trick... is very simple and it works in Excel 2010
CRs REPLACING:
Select the cell range you want for replacing CRs
In the [Find and Replace] dialog go to the [Replace] Tab
- Click in the [Find what:] box and then type CTRL+ENTER (nothing appears)
- In the [Replace with:] box type the replacing text... or leave empty.
For being sure just click [find all] and after click [replace all] or whatever...
to remove the finding text just go to [find what] and press DEL and BACKSPACE
eh voila...
Be happy...
CLEAN does not remove leading or trailing spaces. For this use =TRIM(CLEAN(B2))
i am writing from Excel to text file. how to add new line character?
@Santosh
adding a new line character shouldn't be hard if you have a constant character to key off of. In that case you can use substitute to replace key character with the key character and char(13)
hi how to add new line character?
These methods are great but do not seem to help with the problem I am having. Excel 2010 appends a hidden linefeed 0x0d followed by a hidden carriage return 0x0a at the end of each cell. I have a worksheet that concatenates the values in other cells to form a syntactically correct linux shell script, one shell command in each cell. Then when I output these cells to a text file so I can use them with linux, each line of my linux shell scripts has the 0x0d, 0x0a ending. The linux shell chokes on the 0x0d as I believe linux expects only a 0x0a to terminate each line of input.
The methods here seem to remove unwanted lf and cr but not the ones excel puts at the end of every cell.
Am I using these tools incorrectly?
Thanks
joe
@Joe
Microsoft products place a CRLF to perform line breaks.
You will need to further preprocess the file, I assume a CSV on your Linux machine to remove the Carriage returns.
I used to have to do that when transferring files
I suggest the *nix style command line command tr
tr -d '\r' outfile
Make sure you name your output file a different name to protect your document in case of error.
it looks like my sample code got truncated removing the redirects and input file name
you can find the correct syntax on this page
http://stackoverflow.com/questions/800030/remove-carriage-return-in-unix
GL
Neat! thanks.