CLEAN

Text Functions

(4.7/5)

Removes all non-printable characters from text. Essential for data cleaning, removing control characters, and preparing text data imported from external sources.

Interactive Formula Tester

Test Text:

Formula:

=CLEAN("Hello World")

Complete Theory & Understanding

Master the fundamentals of Excel CLEAN function

Core Concept

The CLEAN function is Excel's text cleaning tool that removes all non-printable characters (ASCII codes 0-31) from text strings. It's essential for data cleaning, removing control characters, and preparing text data imported from external sources.

Why Use CLEAN?

Clean text imported from external sources
Standardize text by removing control characters
Prepare text data for reports and exports
Validate and clean user input data

Key Characteristics

Non-Printable Removal

Removes all characters with ASCII codes 0-31

CLEAN(A1) removes control characters

ASCII Range

Specifically targets ASCII codes 0-31

CLEAN removes tabs, line feeds, carriage returns

Data Import

Essential for cleaning imported data

CLEAN(data from external sources)

Function Combination

Often used with TRIM for comprehensive cleaning

TRIM(CLEAN(A1))

Function Anatomy

=CLEAN(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Data Import Cleaning

Clean text imported from external sources

Text Standardization

Standardize text by removing control characters

Report Preparation

Prepare text data for reports and exports

Data Validation

Validate and clean user input data

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=CLEAN(text)

Required

text:

The text from which to remove non-printable characters

Returns

Return Value:

Text with non-printable characters removed

Description: Removes all non-printable characters from text (characters with ASCII codes 0-31)

Interactive Examples

Basic Character Removal

Remove non-printable characters from text

Input Text:

"Hello World"

Formula:

=CLEAN(A1)

Result:

HelloWorld

Removes tab characters and other non-printable characters

VBA Implementation & Automation

Basic CLEAN in VBA

Simple VBA implementation of CLEAN function

' Basic CLEAN in VBA
Range("C1").Value = Application.WorksheetFunction.Clean(Range("A1").Value)

' Using VBA CLEAN function
Dim text As String
text = "Hello" & Chr(9) & "World"
Range("B1").Value = Application.WorksheetFunction.Clean(text)

' Loop through range and clean text
Sub CleanText()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If Not IsEmpty(cell.Value) Then
            cell.Offset(0, 1).Value = Application.WorksheetFunction.Clean(cell.Value)
        End If
    Next cell
End Sub

' Clean and trim text in VBA
Sub CleanAndTrim()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If Not IsEmpty(cell.Value) Then
            Dim cleanedText As String
            cleanedText = Application.WorksheetFunction.Clean(cell.Value)
            cell.Offset(0, 1).Value = Trim(cleanedText)
        End If
    Next cell
End Sub

Advanced Text Cleaning

Comprehensive text cleaning with custom logic

' Advanced text cleaning function
Function CleanTextAdvanced(text As String) As String
    ' Remove non-printable characters
    Dim cleaned As String
    cleaned = Application.WorksheetFunction.Clean(text)
    
    ' Additional custom cleaning if needed
    ' Remove specific characters or patterns
    
    CleanTextAdvanced = cleaned
End Function

' Clean text from clipboard
Sub CleanClipboardText()
    Dim text As String
    text = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("text")
    Range("A1").Value = Application.WorksheetFunction.Clean(text)
End Sub