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 2016, 2013 - 2003
Select the cell or cells, click Data/Text To Columns form Excel's menu bar, choose the Delimited option on the first dialog page, click the Next button, uncheck everything except Other and type Ctrl+J into the field next to it (you won't see anything in that box, but the column layout chart will show that it is being split at the line feeds (they are not carriage returns). Then just click. How to convert carriage return to comma in Excel? Maybe you have a lot of cells which have the values are separated by carriage returns, and now you need to find all the carriage returns and replace them with commas or other characters as following screenshot shown.
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.
All these 3 ways are really quick. Feel free to pick the one that suites you best:
- Remove all carriage returns manually to quickly delete line breaks in 1 worksheet.
- Delete line breaks using formulas to employ several formulas for a complex cell text processing.
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.
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 Text Toolkit or 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
You may also be interested in:
Related Articles
- 1 Insert Grid Lines on a Spreadsheet
- 2 Find the Y-Intercept of a Line of a Graph in Excel on a Mac
- 3 Enter a Subscript in Excel
- 4 Add Paragraph Breaks in Cells in Excel for OS X
Often when you're entering data into a Microsoft Excel spreadsheet, you want certain cells to span multiple lines, either for addresses, multiline pieces of information or just for readability. But if you press enter in an Excel cell, it will move you to the next cell down rather than inserting a line break.
Tip
- To create a new line in an Excel cell, hold down the alt key on your keyboard while hitting enter.
Insert Line Break in Excel
To move to a new line in an Excel cell, simply type text in the cell as normal and then press enter while holding down the alt key. If you need to move to a specific place in the cell to enter a new line, double-click the cell with your mouse and then click the specific place you want to enter the line break. Then hit enter while holding down alt.
If you need to have a formula output break onto a new line to go into an Excel cell, you can do this as well. To do so, include in the formula the bit '& CHAR(10) &' wherever you want the formula's output to break lines if you're using Windows. That tells Excel to insert the character numbered as 10 in the computer's character set, which on Windows is a line break character. If you're using a MacOS computer, use the number 13 rather than 10 for the same effect.
Wrapping Text in Excel
In some cases, you may have too much text in an Excel cell to fit on one line. In this case, you may want the text to wrap onto another line automatically. To enable this feature, select the cells that you want to automatically wrap. Then, on the 'Home' tab in the ribbon menu, click 'Alignment,' then the 'Wrap Text' button.
If the cell's height is not fixed, the text will automatically wrap from line to line and all the text should be visible. If you need to adjust the cell's height, you can physically drag the edges of the cell using your mouse or using the Cell Size menu.
To do that, select the cells you're interested in and click the 'Home' tab.' Click the 'Cells' group, then click 'Format' and under 'Cell Size,' click 'AutoFit Row Height' to make the cell automatically adjust its height.
If you'd prefer to have a fixed height that might not contain all the text, click 'Row Height' instead of 'AutoFit Row Height' and enter your desired height.
References (4)
About the Author
Steven Melendez is an independent journalist with a background in technology and business. He has written for a variety of business publications including Fast Company, the Wall Street Journal, Innovation Leader and Business BVI. He was awarded the Knight Foundation scholarship to Northwestern University's Medill School of Journalism.
Cite this Article Choose Citation Style
Melendez, Steven. 'How to Insert a Return in an MS Excel Cell.' Small Business - Chron.com, http://smallbusiness.chron.com/insert-return-ms-excel-cell-54309.html. 14 August 2018.
Melendez, Steven. (2018, August 14). How to Insert a Return in an MS Excel Cell. Small Business - Chron.com. Retrieved from http://smallbusiness.chron.com/insert-return-ms-excel-cell-54309.html
Melendez, Steven. 'How to Insert a Return in an MS Excel Cell' last modified August 14, 2018. http://smallbusiness.chron.com/insert-return-ms-excel-cell-54309.html
Note: Depending on which text editor you're pasting into, you might have to add the italics to the site name.