Како уклонити преломе редова (повратак носиоца) из ћелија у Екцелу 2013, 2010 и 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. Алт + Ентер.

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.

Белешка: 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: превоз Повратак (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.

Будите опрезни: Both options are found in Excel. When importing from files Ткт. or . Цсв the data usually contains carriage returns and line feeds. When a line break is entered manually by pressing Алт + Ентер, Excel only inserts a newline character. If the file . Цсв received from a fan of Linux, Unix or other similar system, then prepare for an encounter with only a newline character.

Ручно уклањање вагона

Предности: This method is the fastest.

Против: No extra perks 🙁

This is how you can remove line breaks using the “Нађи и премести":

  1. Select all cells where you want to remove carriage returns or replace them with another character.Како уклонити преломе редова (повратак носиоца) из ћелија у Екцелу 2013, 2010 и 2007
  2. Press Цтрл + Хда бисте отворили оквир за дијалог Нађи и премести (Find and Replace).
  3. Put the cursor in the field Да пронађу (Find what) and press Цтрл + Ј. At first glance, the field will seem empty, but if you look closely, you will see a small dot in it.
  4. У Замењен (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 Замењен (Replace With) empty.Како уклонити преломе редова (повратак носиоца) из ћелија у Екцелу 2013, 2010 и 2007
  5. Притисните дугме Замените (Replace All) and enjoy the result!Како уклонити преломе редова (повратак носиоца) из ћелија у Екцелу 2013, 2010 и 2007

Уклоните преломе редова користећи Екцел формуле

Предности: 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 ПОГЛЕД (LOOKUP).

Против: 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 КСНУМКС линије.
  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)

    Како уклонити преломе редова (повратак носиоца) из ћелија у Екцелу 2013, 2010 и 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:
    • Изаберите све ћелије у колони C и притискајући Цтрл + Ц copy the data to the clipboard.
    • Затим изаберите ћелију B2, притисните пречицу на тастатури Схифт + ФКСНУМКС и онда Убацити (Инсерт).
    • Избришите помоћну колону.

Уклоните преломе редова помоћу ВБА макроа

Предности: Create once – use again and again with any workbook.

Против: 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.

Ostavite komentar