How to Remove Line Breaks in Excel Using CHAR and CLEAN Functions

スポンサーリンク
スポンサーリンク

About CHAR and CLEAN Functions

Overview of CHAR and CLEAN

Remove Line Breaks Excel Functions/Text Manipulation

=CHAR(CodeNumber)

=CLEAN(Text)

CHAR Overview: Returns the character corresponding to the specified numeric code.

CLEAN Overview: Removes non-printable characters, such as line breaks, from text.

  • Use CHAR(10) to insert or remove line breaks.
  • Common CHAR code examples:
    CHAR(8) CHAR(9) CHAR(10) CHAR(32) CHAR(160)
    Backspace Tab Line Break Space Non-breaking Space (HTML)
  • To remove line breaks or other characters, use the SUBSTITUTE function: SUBSTITUTE.
  • To insert a line break in a cell, press Alt + Enter where the break is needed.
  • CLEAN may produce unexpected results since it removes all non-printable characters. Use with caution.

When to Use CHAR

  • To remove line breaks within a cell.

How to Use CHAR

  A B C D E
1    
2   New York

Brooklyn

Downtown

BrooklynDowntown

=SUBSTITUTE(C2, CHAR(10), "")

=CLEAN(C2)

Removes line breaks (CHAR(10)) by replacing them with blank spaces.

3

4

5