[Excel VBA] How to Remove Line Breaks and Special Characters in Cells | Handling vbLf and ChrW

Overview

In Excel, line breaks (created via Alt + Enter) or special characters contained within cells can cause visual discrepancies or processing errors. While manual removal is tedious, VBA allows for batch deletion.

This article introduces how to remove line breaks (vbLf) and special whitespace characters (such as ChrW(160)) using VBA. It also explains how to detect whether a cell contains line breaks.

Sample Code

Sub CleanCellLineBreaks()

    ' Remove line feed (vbLf)
    Range("D3").Value = Replace(Range("D3").Value, vbLf, "")

    ' Remove special non-breaking space (ChrW(160))
    Range("D4").Value = Replace(Range("D4").Value, ChrW(160), "")

    ' Check if the active cell contains a line break
    If InStr(ActiveCell.Value, vbLf) > 0 Then
        MsgBox "This cell contains a line break."
    End If

End Sub

Code Explanation

Replace(..., vbLf, "")

vbLf stands for “Line Feed” and corresponds to the line break character within a cell. This process replaces in-cell line breaks (entered via Alt + Enter) with an empty string, effectively removing them.

Replace(..., ChrW(160), "")

ChrW(160) represents an invisible special whitespace character (Non-Breaking Space / NBSP). This character is often included in data pasted from the Web or copied from other applications. Standard Trim functions often fail to remove this character.

InStr(..., vbLf)

The InStr function checks whether a specific character (in this case, a line break) exists within a string. If the return value is greater than 0, the character exists.

Use Cases

• Formatting data before converting it to CSV.
• Correction processing for VLOOKUP or MATCH functions that fail due to hidden line breaks or special characters.
• Cleaning data imported from the Web.

Important Notes

• Line Break Types: While vbLf is effective for standard cell line breaks (Alt + Enter), some data may use vbCrLf (Carriage Return + Line Feed). If vbLf does not work, try replacing vbCrLf or vbCr as well.
• Invisible Characters: Non-printing characters like ChrW(160) are difficult to detect visually. If standard trimming does not work, specific replacement by character code is required.

Summary

This article demonstrated how to remove line breaks and special whitespace characters in cells using VBA.

• vbLf: Removes standard line codes.
• ChrW(160): Removes invisible non-breaking spaces.
• InStr: Detects and notifies the presence of specific characters.

Please utilize this method when performing data formatting or preprocessing in Excel.