Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Remove Line Breaks in Excel

    How to Remove Line Breaks in Excel

    AsmiBy AsmiFebruary 19, 2023 Excel 5 Mins Read

    Excel allows you to create a different line on the same cell using the Excel shortcut, Alt + Enter. Many users prefer breaking lines to fit multiple characters in the same cell without stretching the cell horizontally.

    However, if you change your mind about the line breaks, you may realize that compiling them together in one line is tedious. Fortunately, you can use some of Excel’s tools and features to speed up the process.

    Clear Format

    Line Breaks are simply a format. If you clear the formatting you’ve applied to the cells, Excel will put your data in the same line again. However, do not use this method if you have other formatting applied to your cells because Excel will all formatting from the select cells.

    1. Head to the Home tab.
    2. Select Clear from the Editing section.
    3. Choose Clear Formats.
      Clear Formats Excel

    Refer Cell with Line Breaks on a Different Cell

    If you’ve used a delimiter, like space, after each element, you can simply use cell referencing. You can also use this method if you do not have a delimiter, however, know that when you compile your data in a single line there will be no symbol separating it.

    For example, cell A2 has about three line breaks. Each element has space in between them. In a different column, we simply entered =A2. This gives us the data in cell A2 without the line breaks. You can then drag the fill handle to convert the remaining values.

    This is because line breaks are a formatting feature. When Excel uses cell referencing, it only references the data and not the format. If you’re looking to delete the original source, WAIT! The reference is still dependent on the cells with the line breaks.

    Paste Static Values

    What you can do as a workaround is paste only Values from the Paste Options. Copy the values from the destination cell range and right-click on it. Use the V key on your keyboard and there you have it! Excel will only paste only the values, without the formula. You can then clear the original source without data in the destination range getting cleared out.

    Use the Find Command

    The Find Command is one of the fastest ways you can remove line breaks from your entire worksheet. The only downside to this method is that it lacks customizability and you do not have the option to remove line breaks in specific cells as it applies to the entire grid.

    1. On your keyboard, use Ctrl + H.
    2. Next to Find what, use the combination Ctrl + J. You might see a small dot of nothing at all.
      Find and Replace Excel
    3. Depending on if you want to add a delimiter, enter a symbol next to Replace with or leave it empty.
    4. Click Replace All.

    Use the SUBSTITUTE Function

    Before we move on to explain this method, let’s learn a bit about ASCII. ASCII, the abbreviation for the American Standard Code for Information Interchange assigns a number for each special character, including Line Breaks.

    The ASCII number for Line Breaks is 10. We will be using this information to replace line breaks with a delimiter, or nothing if you have a delimiter in use. 

    Substitute function with comma

    Column A has five values with line breaks. Here is the formula we used to separate these values in cases where there’s no delimiter.

    =SUBSTITUTE(A2, CHAR(10), “ ”)

    You can modify the formula depending on your cell value and your preferred delimiter. For example, if you do not wish to add an extra space between the values that once had line breaks, replace the third argument from “ ” with “”. Similarly, if you wish to add a comma instead of a space, use “ ,” instead of “ ”  in the third argument.

    SUBSTITUTE Function Excel

    Again, with this method as well, please use Paste Options to paste values if you’re looking to delete the original source.

    Clean Your Cell Using Power Query

    Power Query is an excellent automation tool that will get your work done swiftly. We however only recommend this step if you have entered a separator between your values, or if you prefer to assemble the data without a divider.

    1. Open your worksheet and head to the Data tab.
    2. Select From Table in the Get & Transform section.
      New Query from Table Excel
    3. In the Power Query editor, right-click on your column and choose Transform > Clean.
      Power Query Clean
    4. Close and Load your data from the Home tab.

    Create a Custom Function

    Let’s make removing line breaks easier; let’s create our own function! Excel uses VBA to create functions and macros. You will first have to enable the Developer tab from File > Options > Customize ribbon.

    Enable Developer Tab Excel

    After you enable the Developer tab, you can access the Visual Basic option to create your own function.

    1. Head to the Developer tab and select Visual Basic.
      Visual Basic Excel
    2. Right-click on your sheet from the sidebar and select Insert > Module.
      Insert New Module VBA
    3. Enter the following code on the window to your right:

    Function RBreaklines(cell As String) As String
    Dim Result As String
    Dim count As Integer
    Let celllength = Len(cell)
    For count = 1 To celllength
    If Mid(cell, count, 1) = Chr(10) Then
    cell = Replace(cell, Chr(10), ", ")
    End If
    Next
    RBreaklines = cell
    End Function

    Visual Basic for Applications Excel

    You can then use the function like you would use any other function. As an argument, pass the cell you wish to remove line breaks from and you’re good to go. Here is how your data will look after you pass it using the function.

    Excel Basics
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Clear Format
      • Refer Cell with Line Breaks on a Different Cell
      • Use the Find Command
      • Use the SUBSTITUTE Function
      • Clean Your Cell Using Power Query
      • Create a Custom Function
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.