In this short tutorial, you'll see how to find and replace special characters in Google Sheets using built-in features and powerful formulas. Whether you're cleaning imported data, fixing formatting issues, or standardizing text entries, removing unwanted characters like line breaks, non-printable characters, and special symbols is essential for accurate data analysis and comparisons.

Special characters cause comparison errors, failed VLOOKUP, and sorting issues in spreadsheets, making text cleaning a critical skill.

Problem: Special Characters Breaking Your Data

Unwanted characters from web scraping, CSV imports, or copy-pasting cause invisible problems in Google Sheets.

Common Issues

  • Line breaks (\n) preventing text matching
  • Non-breaking spaces (\u00A0) causing comparison failures
  • Currency symbols interfering with number calculations
  • Invisible characters breaking data validation

Example: "Apple Inc." ≠ "Apple Inc. " (trailing space)

Solution: Methods to Clean Special Characters

1. Find and Replace (UI Method)

Steps:

  1. Press Ctrl+H (Windows) or Cmd+H (Mac)
  2. In Find field: Enter special character or use regex
  3. In Replace field: Leave empty or enter replacement
  4. Click Replace all

For line breaks: Find: \n (enable Search using regular expressions)

For multiple spaces: Find: + Replace: (single space)

2. SUBSTITUTE Function for Single Characters

Use SUBSTITUTE() to remove or replace specific characters in text.

Google Sheets Formula:

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

Removes line breaks (CHAR(10)) and replaces with space.

Multiple substitutions:

=SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", "")

Removes dollar signs and commas from currency values.

3. REGEXREPLACE for Pattern Matching

REGEXREPLACE() provides powerful pattern-based character removal using regular expressions.

Google Sheets Formulas:

Remove all non-numeric characters:

=REGEXREPLACE(A2, "[^0-9]", "")

Remove all special characters, keep letters/numbers:

=REGEXREPLACE(A2, "[^A-Za-z0-9 ]", "")

Remove multiple spaces:

=REGEXREPLACE(A2, " +", " ")

Remove line breaks and tabs:

=REGEXREPLACE(A2, "[\n\t]", "")

4. Python Script for Batch Processing

Automate Google Sheets cleaning using Python with the gspread library.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import re

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)

sheet = client.open('Sales Data').sheet1
values = sheet.col_values(2)

cleaned = [re.sub(r'[^A-Za-z0-9 ]', '', str(v)) for v in values]

for i, val in enumerate(cleaned, start=1):
    sheet.update_cell(i, 3, val)

print(f"Cleaned {len(cleaned)} cells")

Output Result:

Cleaned 150 cells