Faʻafefea ona aveese laina laina (toe foʻi mai le taavale) mai sela i Excel 2013, 2010 ma 2007

This tutorial will introduce you to three ways to remove carriage returns from cells in Excel. You will also learn how to replace line breaks with other characters. All suggested solutions work in Excel 2013, 2010, 2007 and 2003.

Line breaks can appear in text for various reasons. Usually carriage returns occur in a workbook, for example when text is copied from a web page, when they are already in a workbook received from a client, or when we ourselves add them by pressing keys. Alt + Ulufale.

Whatever the reason for them, the challenge now is to remove carriage returns, as they interfere with phrase searches and result in column clutter when wrapping is enabled.

All three presented methods are quite fast. Choose the one that suits you best:

  • We remove all line breaks manually to quickly put things in order on one sheet.
  • We remove line breaks using formulas and thus set up complex processing of text data in cells.
  • We use a VBA macro to clear line breaks from several workbooks.

Manatua: Initially, the terms “Carriage return” and “Line feed” were used when working on typewriters and denoted two different operations. An inquisitive reader can independently find detailed information about this on the Internet.

Computers and word processing software were designed with the characteristics of typewriters in mind. This is why two different non-printable characters are now used to indicate a line break: toe faafoi taavale (Carriage return, CR or ASCII code 13) and Line translation (Line feed, LF or ASCII code 10). On Windows, both characters are used together, and on *NIX systems, only newlines are used.

Faaeteete: Both options are found in Excel. When importing from files .xtxt or . Csv the data usually contains carriage returns and line feeds. When a line break is entered manually by pressing Alt + Ulufale, Excel only inserts a newline character. If the file . Csv received from a fan of Linux, Unix or other similar system, then prepare for an encounter with only a newline character.

O le aveeseina o le taavale e toe fo'i ma le lima

tulaga e lelei: This method is the fastest.

leaga: No extra perks 🙁

This is how you can remove line breaks using the “Suʻe ma sui":

  1. Select all cells where you want to remove carriage returns or replace them with another character.Faʻafefea ona aveese laina laina (toe foʻi mai le taavale) mai sela i Excel 2013, 2010 ma 2007
  2. nusipepa Ctrl + He aumai i luga se pusa talanoa Suʻe ma sui (Saili ma Sui).
  3. Tuu le pupuni i le fanua Ina ia maua (Find what) and press Ctrl+J. At first glance, the field will seem empty, but if you look closely, you will see a small dot in it.
  4. I le Suia e (Replace With) enter any value to insert in place of carriage returns. Usually a space is used for this to avoid accidental gluing of two adjacent words. If you just want to remove line breaks, leave the field Suia e (Replace With) empty.Faʻafefea ona aveese laina laina (toe foʻi mai le taavale) mai sela i Excel 2013, 2010 ma 2007
  5. Kiliki le faʻamau Sui uma (Replace All) and enjoy the result!Faʻafefea ona aveese laina laina (toe foʻi mai le taavale) mai sela i Excel 2013, 2010 ma 2007

Aveese laina laina e faʻaaoga ai Excel formula

tulaga e lelei: You can use sequential or nested formulas for complex text verification in the processed cell. For example, you can remove carriage returns and then find extra leading or trailing spaces, or extra spaces between words.

In some cases, line breaks must be removed in order to later use the text as function arguments without making changes to the original cells. The result can be used, for example, as a function argument VIEW (LOOKUP).

leaga: You will need to create a helper column and perform many additional steps.

  1. Add an auxiliary column at the end of the data. In our example, it will be called 1 laina.
  2. In the first cell of the auxiliary column (C2), enter the formula to remove/replace line breaks. Below are a few useful formulas for various occasions:
    • This formula is suitable for use with Windows and UNIX carriage return/line feed combinations.

      =ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);"")

      =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")

    • The following formula is suitable for replacing a line break with any other character (for example, “, ” – comma + space). In this case, the lines will not be combined and extra spaces will not appear.

      =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);", ")

      =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")

    • And this is how you can remove all non-printable characters from text, including line breaks:

      =ПЕЧСИМВ(B2)

      =CLEAN(B2)

    Faʻafefea ona aveese laina laina (toe foʻi mai le taavale) mai sela i Excel 2013, 2010 ma 2007

  3. Copy the formula to all cells in the column.
  4. Optionally, you can replace the original column with a new one, with line breaks removed:
    • Filifili sela uma i se koluma C ma le oomi Ctrl + C copy the data to the clipboard.
    • Sosoo ai, filifili se sela B2, fetaomi le ala pupuu keyboard Sifi + F10 ae te'i ane faaofi (Faaofiofi).
    • Aveese le koluma fesoasoani.

Aveese laina vaeluaga ile VBA macro

tulaga e lelei: Create once – use again and again with any workbook.

leaga: At least basic knowledge of VBA is required.

The VBA macro in the following example removes carriage returns from all cells on the active worksheet.

Sub RemoveCarriageReturns()      Dim MyRange As Range      Application.ScreenUpdating = False      Application.Calculation = xlCalculationManual         For Each MyRange In ActiveSheet.UsedRange          If 0 < InStr(MyRange, Chr(10)) Then              MyRange = Replace(MyRange, Chr(10), "")          End If      Next         Application.ScreenUpdating = True      Application.Calculation = xlCalculationAutomatic  End Sub

If you are not too familiar with VBA, I recommend that you study the article on how to insert and execute VBA code in Excel.

Tuua se tali