Cleaning data in Google Sheets: How to Remove Spaces Using SUBSTITUTE

Jul 23

Whether you're working with names, addresses, or imported datasets, extra spaces can wreak havoc on your data analysis. These unnecessary spaces might cause formulas to break, prevent proper sorting or filtering, or make your data appear inconsistent. Thankfully, Google Sheets offers a straightforward way to fix this using the SUBSTITUTE function.

In this article, we’ll explore how to use the SUBSTITUTE function to remove spaces from text, discuss its advantages and limitations, and suggest alternatives for more complex cleaning tasks.

The SUBSTITUTE function in Google Sheets replaces instances of one text string with another. Its syntax is:

=SUBSTITUTE(text, old_text, new_text, [instance_number])

text: The cell or text you want to clean.
old_text: The characters you want to replace (e.g., a space " ").
new_text: What you want to replace it with (e.g., an empty string "").
[instance_number]: (Optional) Which occurrence to replace. If omitted, all are replaced.

How to Use SUBSTITUTE to Remove Spaces

To remove all spaces from a cell:

=SUBSTITUTE(A1, " ", "")

This will return the contents of cell A1 with every space removed, even between words.

Example:

In an ideal world, you’d have a clean dataset where unnecessary white spaces like those in between numbers are removed. But, that may not be the case, and this is where the SUBSTITUTE function will be helpful.

Text in cell A1: 102 323

Formula: =SUBSTITUTE(A1, " ", "")

Output: JohnSmith

Pros of Using SUBSTITUTE

Simple and fast: Easy to implement for small or medium-sized datasets.
Targeted: Can remove specific characters (not just spaces).
Flexible: Optional parameter allows you to remove only the nth instance if needed.

Cons and Limitations

Removes all spaces: Not ideal if you want to preserve spacing between words (e.g., first and last names).
No trimming: It doesn’t handle other common whitespace issues like leading or trailing spaces unless you specifically replace them.
Doesn’t handle non-breaking spaces (CHAR(160)): Imported data (like from websites or PDFs) might contain non-breaking spaces that " " won’t catch.

Alternatives to SUBSTITUTE for Data Cleaning

1. TRIM Function – For Leading and Trailing Spaces

If your data only has extra spaces at the beginning or end of a string—but you want to keep the space between words—use:

TRIM removes:

Leading spaces
Trailing spaces
Replaces multiple spaces between words with a single space

2. REGEXREPLACE – For Advanced Pattern Matching

To remove all whitespace characters, including non-breaking spaces or tabs:

=REGEXREPLACE(A1, "\s+", "")

This will remove:

Regular spaces
Tabs
Non-breaking spaces (CHAR(160))
Newlines

3. CLEAN Function – To Remove Non-printable Characters

If your data contains invisible formatting characters:

=CLEAN(A1)

While CLEAN doesn’t remove spaces, it's useful when you're troubleshooting messy data with invisible characters.

Pro Tip: Combine Functions for Best Results

You can combine functions for more powerful cleaning:

=SUBSTITUTE(TRIM(CLEAN(A1)), " ", "")

Or use:

=REGEXREPLACE(TRIM(CLEAN(A1)), "\s+", "")

This removes:

Non-printable characters
Extra spaces
All whitespace if needed

When to Use SUBSTITUTE

Use SUBSTITUTE when:

You want complete control over which character to remove.
You’re working with consistent formatting (e.g., removing all hyphens from phone numbers or all spaces in postal codes).
You only need to remove specific, predictable characters.

Avoid using it when:

You want to keep word spacing but clean the rest (use TRIM).
You’re unsure what kind of spaces/characters are present (use REGEXREPLACE).

Final Thoughts

Removing spaces using SUBSTITUTE in Google Sheets is a quick and effective way to clean data when you need precision. But it’s not a one-size-fits-all solution. Understanding when to use SUBSTITUTE, and when to switch to more robust functions like TRIM or REGEXREPLACE, can help you maintain clean, reliable datasets for better analysis.

Have a messy sheet? Try out these formulas, and watch your data become cleaner and more usable in seconds.

FWD EDITORS

We’re a team of data enthusiasts and storytellers. Our goal is to share stories we find interesting in hopes of inspiring others to incorporate data and data visualizations in the stories they create.