ווי צו באַזייַטיקן שורה ברייקס (וועגן קערט) פון סעלז אין עקססעל 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 . Csv 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 . Csv 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.
2. דרוק Ctrl + Hצו ברענגען אַרויף אַ דיאַלאָג קעסטל געפֿינען און פאַרבייַטן (Find and Replace).
3. Put the cursor in the field צו געפינען (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. אין די ריפּלייסט דורך (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.
5. דריקן פאַרבייַטן אַלע (Replace All) and enjoy the result!

אַראָפּנעמען שורה ברייקס ניצן עקססעל פאָרמולאַס

פּראָס: 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).

קאָנס: 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)

   

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 און דרינגלעך Ctrl + C copy the data to the clipboard.
   • ווייַטער, אויסקלייַבן אַ צעל B2, דריקן די קלאַוויאַטור דורכוועג שיפט + פקסנומקס און דעמאָלט אַרייַנלייגן (אַרייַנלייגן).
   • ויסמעקן די העלפּער זייַל.

אַראָפּנעמען שורה ברייקס מיט VBA מאַקראָו

פּראָס: 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.