CLEAN function

PRO BASIC

The CLEAN function in Jspreadsheet Formulas Pro is a handy tool that helps you tidy up your text data. It works by removing all non-printable ASCII characters (character codes 0–31) from a text string, leaving only visible and printable characters. This is useful for cleaning up text copied from web pages, external data sources, or files that include hidden formatting. This is particularly useful when you're dealing with text data imported from other sources, which might include nonprintable characters that could interfere with other functions or formulas. With the CLEAN function, you can ensure your text data is clean and ready for further processing.

Documentation

Removes all nonprintable characters from a text string.

Syntax

CLEAN(text)

Parameter	Description
`text`	The text string that contains the characters you want to remove.

Behavior

The CLEAN function is used to remove all non-printable characters from a text string. It works by taking a single argument, which is the text string from which you wish to remove non-printable characters. The function then returns a new string that is identical to the input string, apart from the fact that all non-printable characters have been removed.

Empty cells: If the CLEAN function is applied to an empty cell, the result will also be an empty cell.
Text: The CLEAN function treats text strings as input and removes all non-printable characters from them.
Numbers: If the CLEAN function is applied to a cell containing a numeric value, the function will treat the numeric value as a text string, and since numeric values do not contain non-printable characters, the function will return the original numeric value.
Booleans: Booleans are coerced to text first (e.g., TRUE becomes "TRUE"), and since these contain no non-printable characters, the function returns the string "TRUE".
Errors: If the cell to which the CLEAN function is applied contains an error, the function will return that error.

Common Errors

Error	Description
#VALUE!	This error occurs when the `CLEAN` function is applied to a cell that contains an error.

Best practices

Always ensure that the text string to which the CLEAN function is being applied does not contain any characters that you do not want to be removed. The CLEAN function will remove all non-printable characters, and there is no way to specify particular non-printable characters that you want to keep.

Keep in mind that the CLEAN function treats all values as text strings. Therefore, if you apply the function to a numeric value, the function will return the numeric value, not a text string.

Use the CLEAN function in conjunction with other text functions for best results. For instance, you can first use the CLEAN function to remove non-printable characters from a text string, and then use another function to perform further manipulation on the cleaned text string.

Be careful when applying the CLEAN function to a cell that contains an error. The function will simply return the error, so it is always a good idea to ensure that the cell does not contain any errors before applying the CLEAN function.

Usage

A few examples using the CLEAN function.

CLEAN("This is a test.")  
// Removes hidden characters (e.g., zero-width space), returns "This is a test."

CLEAN(A1)  
// Cleans non-printable characters from the text in cell A1

CLEAN(CHAR(7) & "Hello" & CHAR(10))  
// Returns "Hello" (removes bell and line break characters)

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MzU2NWQxY2FmNTQyYzhhNDYwYzQxYzkzNDlhM2E0MDJhNjI5ZjQ3M2ZjZjZiNTkxYTk4YWIyYzA4MmZkNGU4ZTUyOTJjNDMyZmEzODM4ZDY5YzEwM2M2MmI2MmM2YzgyZDFhYWRmNGQzZTMxYzBjNmY4YjNiMDc0MTdiZjNmMmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Original Text",
        "Cleaned Text"
    ],
    [
        "Product\u200bName\u0001",
        "=CLEAN(A2)"
    ],
    [
        "Customer\u0002Data\u0003",
        "=CLEAN(A3)"
    ],
    [
        "Invoice\u0007Number",
        "=CLEAN(A4)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('MzU2NWQxY2FmNTQyYzhhNDYwYzQxYzkzNDlhM2E0MDJhNjI5ZjQ3M2ZjZjZiNTkxYTk4YWIyYzA4MmZkNGU4ZTUyOTJjNDMyZmEzODM4ZDY5YzEwM2M2MmI2MmM2YzgyZDFhYWRmNGQzZTMxYzBjNmY4YjNiMDc0MTdiZjNmMmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Original Text",
        "Cleaned Text"
    ],
    [
        "Product\u200bName\u0001",
        "=CLEAN(A2)"
    ],
    [
        "Customer\u0002Data\u0003",
        "=CLEAN(A3)"
    ],
    [
        "Invoice\u0007Number",
        "=CLEAN(A4)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('MzU2NWQxY2FmNTQyYzhhNDYwYzQxYzkzNDlhM2E0MDJhNjI5ZjQ3M2ZjZjZiNTkxYTk4YWIyYzA4MmZkNGU4ZTUyOTJjNDMyZmEzODM4ZDY5YzEwM2M2MmI2MmM2YzgyZDFhYWRmNGQzZTMxYzBjNmY4YjNiMDc0MTdiZjNmMmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Original Text",
        "Cleaned Text"
    ],
    [
        "Product\u200bName\u0001",
        "=CLEAN(A2)"
    ],
    [
        "Customer\u0002Data\u0003",
        "=CLEAN(A3)"
    ],
    [
        "Invoice\u0007Number",
        "=CLEAN(A4)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('MzU2NWQxY2FmNTQyYzhhNDYwYzQxYzkzNDlhM2E0MDJhNjI5ZjQ3M2ZjZjZiNTkxYTk4YWIyYzA4MmZkNGU4ZTUyOTJjNDMyZmEzODM4ZDY5YzEwM2M2MmI2MmM2YzgyZDFhYWRmNGQzZTMxYzBjNmY4YjNiMDc0MTdiZjNmMmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56Y3pPVFExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Original Text",
        "Cleaned Text"
    ],
    [
        "Product\u200bName\u0001",
        "=CLEAN(A2)"
    ],
    [
        "Customer\u0002Data\u0003",
        "=CLEAN(A3)"
    ],
    [
        "Invoice\u0007Number",
        "=CLEAN(A4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub