In this tip you’ll discover 3 methods to eliminate carriage returns from Excel cells. Additionally, you’ll learn how to swap line breaks with other symbols. All these solutions are compatible with Excel 365, 2021, 2019, and older versions.

Line breaks in your text might occur for various reasons. Typically, carriage returns show up when you copy text from a webpage, receive a workbook containing line breaks from a client, or manually input them using Alt Enter.

Regardless of the cause, your primary goal now is to remove these carriage returns because they prevent you from finding a phrase and make column content look messy when the wrap text option is enabled.

Please note that originally the terms “Carriage return” and “Line feed” referred to actions on a typewriter and denoted two distinct functions, which you can learn more about on Wikipedia.

Computers and text processing programs were designed considering typewriter characteristics. Therefore, two separate non-printable symbols are now used to signify a line break: “Carriage return” (CR, ASCII code 13) and “Line Feed” (LF, ASCII code 10). Windows employs both symbols consecutively: CR LF, while LF is utilized in *NIX systems. Be cautious: in Excel, you may encounter both formats. If you import data from a .txt or .csv file, you are more likely to find Carriage Return Line Feed. When you insert a line break using Alt Enter, Excel inserts Line Feed exclusively.

If you obtain .csv files from someone using Linux, Unix, etc., you will find only Line Feeds again.

All three methods mentioned are quite rapid. Feel free to choose the one that works best for you:

Manually Remove Carriage Returns

Advantages: the quickest method.

Disadvantages: lacks additional features.

Here are the steps to eliminate line breaks using Find and Replace:

1. Choose all cells where you wish to remove or replace carriage returns.
2. Press Ctrl H to open the Find & Replace dialog box.
3. In the Find What field enter Ctrl J. It will appear blank, but you'll notice a small dot.
4. In the Replace With field, enter any value to substitute carriage returns. Generally, it’s a space to prevent accidental merging of words. If your aim is solely to delete the line breaks, leave the "Replace With" field blank.
5. Click the Replace All button and admire the outcome!

Eliminate Line Breaks Using Excel Formulas

Advantages: you can utilize a formula chain / nested formulas for complex cell text manipulation. For instance, it's feasible to remove carriage returns and then eliminate unnecessary leading and trailing spaces and those between words.

Alternatively, you might need to remove carriage returns to use your text as an argument for another function without altering the original cells. For example, if you desire to use the result as an argument for the =lookup () function.

Disadvantages: you'll need to create a helper column and follow additional steps.

1. Add a helper column to the end of your data. You can label it "1 line".

2. In the first cell of the helper column (C2), enter the formula to remove / replace line breaks. Below are some useful formulas for different scenarios:

   • Handle both Windows and UNIX carriage return/ line feed combinations.=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
   • The subsequent formula will assist in replacing line breaks with any other symbol (comma space). In this case, lines won’t merge and extra spaces won’t appear. =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")
   • If you wish to remove all non-printable characters from text, including line breaks:=CLEAN(B2)

   

3. Copy the formula across the other cells in the column.

4. 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 simply press V.
   • Remove the helper column.

VBA Macro to Remove Line Breaks

Advantages: once created, can be reused in any workbook.

Disadvantages: requires basic knowledge of VBA.

The VBA macro in the example below removes carriage returns from all cells in the currently active worksheet. Sub RemoveCarriageReturns() Dim MyRange As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each MyRange In ActiveSheet.UsedRange If 0

If you’re unfamiliar with VBA, refer to How to insert and run VBA code in Excel.

Remove Carriage Returns with Text Toolkit

If you’re fortunate enough to be a user of our Ultimate Suite for Excel, then you don’t need to spend time on any of the aforementioned procedures. All it takes is these three quick steps:

1. Select one or more cells where you want to delete line breaks.
2. On your Excel ribbon, go to the Ablebits Data tab > Text group, and click the Convert button.
3. 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 substituting each line break with a space, so you place the cursor in the box and press the Enter key:

As a result, you will have a neatly organized table with single-line addresses:

If you're eager to try this and 60 more productivity-enhancing tools for Excel, feel free to download a trial version of our Ultimate Suite. You’ll be amazed to uncover easy-to-use solutions for the most challenging and tedious tasks in Excel!

Video: How to remove line breaks in Excel

The above is the detailed content of 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog. For more information, please follow other related articles on the PHP Chinese website!