How to Clean and Normalize Messy Data in Google Sheets (Step-by-Step)

Published on | ProSheetHub

Working with imported CSVs, web form responses, or data manually typed by multiple team members almost always results in a mess. Inconsistent capitalization, accidental trailing spaces, hidden non-printing line breaks, and mixed date formats can break your formulas, derail VLOOKUP matches, and corrupt pivot tables.

Instead of manually retyping rows or spending hours fixing text cell-by-cell, Google Sheets provides a suite of powerful text-cleaning functions and automated cleanup tools. This guide walks you through the essential formulas and techniques to transform messy raw data into a clean, normalized dataset.

Step 1: Eliminate Invisible Spaces with TRIM & CLEAN

Extra leading spaces, double spaces between words, or trailing spaces are the #1 reason VLOOKUP or XLOOKUP formulas return frustrating #N/A errors. They look identical to the naked eye, but Google Sheets views "John Smith" and "John Smith " as completely different values.

1. The TRIM Function

Removes all leading and trailing spaces, and reduces multiple middle spaces between words to a single space:

=TRIM(A2)

2. The CLEAN Function

Removes non-printable ASCII control characters (such as hidden line breaks or tab characters often introduced when copying data from web pages or PDFs):

=CLEAN(A2)

3. The Power Combo (TRIM + CLEAN)

Nest both functions together to strip both invisible spaces and non-printing line breaks in one go:

=TRIM(CLEAN(A2))

Step 2: Standardize Capitalization (PROPER, LOWER, UPPER)

Mixed case entries (e.g., "jOhn sMiTh", "JOHN SMITH", "john smith") look unprofessional in client emails and reporting dashboards. Google Sheets offers three simple text formatting functions to normalize text casing:

Function Formula Syntax Raw Example Clean Result
PROPER =PROPER(A2) "NEW YORK CITY" "New York City"
LOWER =LOWER(A2) "Admin@DOMAIN.Com" "admin@domain.com"
UPPER =UPPER(A2) "us-east-1" "US-EAST-1"
Pro Tip: Combine trimming and proper casing into a single master normalization formula: 
=PROPER(TRIM(CLEAN(A2)))

Step 3: Strip Out Special Characters & Symbols (REGEXREPLACE)

When customer phone numbers or SKU IDs contain inconsistent symbols (e.g., "+1 (555) 019-2831" vs "555.019.2831"), regular expressions (Regex) allow you to strip out non-numeric characters effortlessly.

Keep Only Digits (Clean Phone Numbers or IDs):

To remove all parentheses, dashes, dots, and spaces, leaving only raw numerical digits, enter:

=REGEXREPLACE(A2, "[^\d]", "")

Regex Pattern Breakdown:

  • [^\d]: Matches any character that is NOT a numerical digit (0–9).
  • "": Replaces those matched symbols with nothing (deletes them).

Step 4: Deduplicate Rows Using Built-In Data Cleanup Tools

Once your text is clean and standardized, remove duplicate entries from your sheet without writing complex formulas:

  1. Highlight your entire dataset range (e.g., A1:D500).
  2. Go to the top navigation menu and click Data > Data cleanup > Remove duplicates.
  3. Check the box if your data has a header row.
  4. Select the key columns to evaluate (e.g., check Email Address or Customer ID).
  5. Click Remove duplicates. Google Sheets will report how many duplicate rows were identified and purged.

Step 5: Replace Formulas with Static Clean Values

After running normalization formulas down your sheet, convert your formula outputs into static, permanent text values so you can safely delete the original messy columns:

  1. Highlight your clean formula output column (e.g., B2:B500) and press Ctrl + C (or Cmd + C on Mac) to copy.
  2. Right-click on the same highlighted range (or target column).
  3. Select Paste special > Values only (or press Ctrl + Shift + V).

Your cells now hold static, clean data rather than dynamic dependencies, freeing up processing speed and allowing you to safely delete your raw scratch columns.

Master Data Cleanup Summary Table

Cleanup Goal Recommended Formula / Tool
Remove Spaces & Line Breaks =TRIM(CLEAN(A2))
Standardize Name Capitalization =PROPER(TRIM(A2))
Clean Email Addresses =LOWER(TRIM(A2))
Extract Digits Only =REGEXREPLACE(A2, "[^\d]", "")
Purge Duplicate Records Data > Data cleanup > Remove duplicates

Comments

Post a Comment