How to Remove Special Characters and Clean Text in Google Sheets

Special characters in Google Sheets can wreak havoc on your data. Whether it's curly quotes from a Word paste, hidden line breaks from a CSV import, emoji characters in form responses, or stray symbols from a web scrape — dirty text leads to broken formulas, failed lookups, and unreliable data.

In this comprehensive guide, you'll learn 5 methods to remove special characters and clean text in Google Sheets — from targeted formula fixes to fully automated cleanup solutions.

Why Special Characters Cause Problems

Special characters aren't just an aesthetic issue. They cause real, measurable problems in your spreadsheets:

• VLOOKUP failures: A hidden space or non-breaking space character makes "John" ≠ "John " — causing lookup misses even though the values look identical.
• Broken CSV exports: Commas, quotes, and line breaks inside cells corrupt CSV file structure when exporting.
• API errors: Sending data with special characters to external services via Zapier, Make, or API integrations often causes encoding errors.
• Sorting inconsistencies: Symbols like @, #, and & sort before letters, pushing important rows to unexpected positions.
• Duplicate false positives: "O'Brien" and "O'Brien" (with a curly apostrophe) appear as two different entries but are the same person.
• Database import errors: Many databases reject characters like \, ", or null bytes during import.

Method 1: SUBSTITUTE (Remove Specific Characters)

The SUBSTITUTE function replaces specific characters with something else — or nothing at all (effectively removing them).

Basic Syntax:

=SUBSTITUTE(text, old_text, new_text)

Examples:

// Remove all hyphens
=SUBSTITUTE(A2, "-", "")
// "555-123-4567" → "5551234567"

// Remove dollar signs
=SUBSTITUTE(A2, "$", "")
// "$1,299.99" → "1,299.99"

// Replace curly quotes with straight quotes
=SUBSTITUTE(SUBSTITUTE(A2, CHAR(8220), CHAR(34)), CHAR(8221), CHAR(34))
// "Hello" → "Hello"

// Remove multiple characters (nested SUBSTITUTE)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "@", ""), "#", ""), "&", "")
// "Price: $99 @store #sale & more" → "Price: $99 store sale  more"

Best for: Removing 1-3 specific, known characters.
Limitation: Gets messy when removing many different characters. Case-sensitive by default.

Method 2: CLEAN Function (Non-Printable Characters)

The CLEAN function removes non-printable ASCII characters (character codes 0-31) from text. These invisible characters often sneak in from data imports, copy-paste operations, and web scraping.

Syntax:

=CLEAN(text)

Common Use: Combine with TRIM

// Remove non-printable characters AND extra spaces
=TRIM(CLEAN(A2))
// "  Hello\n\tWorld  " → "Hello World"

What CLEAN Removes:

• Tab characters (CHAR(9))
• Line feeds (CHAR(10))
• Carriage returns (CHAR(13))
• Null characters (CHAR(0))
• All other ASCII control characters (codes 0-31)

Best for: Cleaning imported data with hidden control characters.
Limitation: Only handles ASCII 0-31. Doesn't remove Unicode special characters, emoji, or visible symbols.

Method 3: REGEXREPLACE (Pattern-Based Removal)

REGEXREPLACE is the most powerful formula for text cleaning. It uses regular expressions to match and remove patterns of characters.

Syntax:

=REGEXREPLACE(text, regular_expression, replacement)

Essential Patterns:

// Remove ALL non-alphanumeric characters (keep letters, numbers, spaces)
=REGEXREPLACE(A2, "[^a-zA-Z0-9 ]", "")
// "Hello! @World #2026 (test)" → "Hello World 2026 test"

// Remove only numbers
=REGEXREPLACE(A2, "[0-9]", "")
// "Order #12345 shipped" → "Order # shipped"

// Remove only letters (keep numbers and symbols)
=REGEXREPLACE(A2, "[a-zA-Z]", "")
// "Order #12345 shipped" → " #12345 "

// Remove emoji and Unicode symbols
=REGEXREPLACE(A2, "[^\x00-\x7F]", "")
// "Hello 🌍 World! 🎉" → "Hello  World! "

// Remove HTML tags
=REGEXREPLACE(A2, "<[^>]+>", "")
// "Bold and italic" → "Bold and italic"

// Remove extra spaces (multiple spaces to single)
=REGEXREPLACE(A2, " +", " ")
// "Hello    World" → "Hello World"

// Remove leading/trailing special characters
=REGEXREPLACE(A2, "^[^a-zA-Z0-9]+|[^a-zA-Z0-9]+$", "")
// "---Hello World!!!" → "Hello World"

Best for: Flexible, pattern-based text cleaning. The most versatile option.
Limitation: Regex syntax has a learning curve. Patterns can break with unexpected data.

Method 4: Apps Script for Bulk Text Cleaning

For cleaning entire columns or sheets at once — replacing formula results with clean values — use Apps Script.

Steps:

1. Open your spreadsheet.
2. Go to Extensions → Apps Script.
3. Paste the following script:

function cleanSpecialCharacters() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var cleaned = 0;

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (typeof values[i][j] === 'string') {
        var original = values[i][j];
        // Remove non-printable characters
        var clean = original.replace(/[\x00-\x1F\x7F]/g, '');
        // Replace non-breaking spaces with regular spaces
        clean = clean.replace(/\u00A0/g, ' ');
        // Remove zero-width characters
        clean = clean.replace(/[\u200B-\u200D\uFEFF]/g, '');
        // Trim extra spaces
        clean = clean.replace(/\s+/g, ' ').trim();

        if (clean !== original) {
          values[i][j] = clean;
          cleaned++;
        }
      }
    }
  }

  range.setValues(values);
  SpreadsheetApp.getUi().alert(
    'Done! Cleaned ' + cleaned + ' cells with special characters.'
  );
}

4. Click Run and authorize the script.
5. The script scans every cell, removes hidden characters, and replaces the values in-place.

Best for: One-time deep cleaning of large datasets with lots of hidden characters.
Limitation: Destructive, no undo, replaces formulas with values, requires coding.

Method 5: CleanSheet Automated Text Cleanup (Easiest)

For ongoing, automated text cleanup without formulas or scripts, CleanSheet provides no-code text formatting rules that run in the background.

Steps:

1. Install CleanSheet from the Google Workspace Marketplace (free trial).
2. Open your spreadsheet and launch CleanSheet from the Extensions menu.
3. Create a text cleanup rule:
   • Select the columns to clean
   • Choose formatting actions: trim whitespace, fix capitalization, or clean text
   • Set the scope (specific columns or entire sheet)
4. Click "Preview" to see what changes will be made.
5. Click "Run" to apply the cleanup.
6. Enable scheduled auto-run to clean new data automatically.

Best for: Automated, ongoing text cleanup without formulas or coding.
Advantage: Preview, scheduled auto-run, handles whitespace and capitalization, no formulas required.

Ready-to-Use Formula Recipes

Copy-paste these formulas for common text cleaning scenarios:

Method Comparison

Frequently Asked Questions

How do I remove special characters from text in Google Sheets?

Use the REGEXREPLACE formula: =REGEXREPLACE(A1, "[^a-zA-Z0-9 ]", "") to remove all non-alphanumeric characters. For specific characters, use SUBSTITUTE: =SUBSTITUTE(A1, "@", ""). For automated bulk cleanup, use CleanSheet's text formatting rules.

What is the CLEAN function in Google Sheets?

The CLEAN function removes non-printable ASCII characters (codes 0-31) from text. Syntax: =CLEAN(A1). It's useful for cleaning data imported from external sources that may contain hidden control characters like tabs, line breaks, and null bytes.

How do I remove line breaks from cells in Google Sheets?

Use the formula =SUBSTITUTE(SUBSTITUTE(A2, CHAR(10), " "), CHAR(13), " ") to replace both types of line breaks (LF and CR) with spaces. Combine with TRIM to remove any extra spaces: =TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(10), " "), CHAR(13), " ")).

Can I clean text in Google Sheets automatically on a schedule?

Yes! Use CleanSheet to set up automated text cleanup rules that run on a schedule (every 1, 6, 12, or 24 hours). It can trim whitespace, fix capitalization, and clean formatting automatically — even when your computer is off. No formulas to maintain or scripts to debug.

Related Articles

• How to Automatically Clean Capitalization and Spaces in Google Sheets
• Google Sheets Trim Whitespace: How to Remove Extra Spaces Automatically
• How to Split and Separate Data in Google Sheets Columns Automatically