How to clean imported data by removing unwanted characters in Google Sheets

[OwenNebula55]

I’m working with a Google Sheets file where I need to import data from an external source. The problem is that when the data comes in, it contains unwanted quotation marks and equals symbols that I need to clean up.

When I use just the basic import function, everything works fine and I get all my data, but it has these extra characters I don’t want:

=IMPORTDATA("https://example-api.com/data?format=csv&id=5432")

This brings in all the information but with quotes and equals signs mixed in.

If I try to clean just one type of character, it works but only removes that one character:

=SUBSTITUTE(IMPORTDATA("https://example-api.com/data?format=csv&id=5432"),CHAR(61),"")

This removes the equals signs but I only get one cell instead of all my data.

When I try to remove both types of characters at once, I run into the same issue:

=SUBSTITUTE(SUBSTITUTE(IMPORTDATA("https://example-api.com/data?format=csv&id=5432"),CHAR(61),""),CHAR(34),"")

This cleans both the quotes and equals signs, but again I only get a single cell of data instead of the full dataset.

Right now I’m working around this by importing the raw data first, then using separate formulas to clean each cell:

=SUBSTITUTE(SUBSTITUTE(A15,CHAR(34),""),CHAR(61),"")

This method works but it’s messy and I have to apply it to every single cell. I’d really prefer to handle the cleaning in one step if possible. Is there a way to make the substitute function work with imported data without losing most of my results?

[Liam_25Meditation]

had the same issue with messy csv imports. try using split after you clean the data - the structure can get messed up during substitution. something like =SPLIT(SUBSTITUTE(SUBSTITUTE(IMPORTDATA("url"),CHAR(34),""),CHAR(61),""),",") works better than just arrayformula for keeping your columns intact.

[Tom42Gamer]

Hit this exact problem last month pulling data from a third-party API. It’s not just SUBSTITUTE - it’s how Sheets handles the imported data format. Your CSV source might be double-encoding characters or wrapping values wrong.

Before getting into complex formulas, check if you can tweak the import URL parameters. Lots of APIs let you specify output format options like &quotes=none or &clean=true. Try different format parameters in your API call first.

If that doesn’t work, I had better luck with REGEXREPLACE instead of nested SUBSTITUTE functions:

=REGEXREPLACE(IMPORTDATA("your-url"), "[\"=]", "")

This strips both quotes and equals signs in one shot and keeps cell structure better than multiple SUBSTITUTE calls. The regex approach catches edge cases where characters show up in weird combinations that nested SUBSTITUTE misses.

[DancingBird]

This happens because SUBSTITUTE treats your entire imported dataset as one big text string instead of keeping it as separate cells. When you wrap SUBSTITUTE around IMPORTDATA, Google Sheets mashes all your data together into a single cell.

Use ARRAYFORMULA to fix this. Try:

=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(IMPORTDATA("https://example-api.com/data?format=csv&id=5432"),CHAR(34),""),CHAR(61),""))

ARRAYFORMULA makes Google Sheets apply your SUBSTITUTE operations to each cell separately while keeping the original structure intact. You’ll get your cleaned data spread across multiple cells like you want.

If that doesn’t work, try a two-step approach: import your data to one range first, then use ARRAYFORMULA on a different range that references the imported data. Sometimes complex transformations work better when you split them up instead of cramming everything into one formula.