How to Clean Data in Google Sheets with QUERY, REGEX and IMPORTRANGE

By Andrew Apell - Updated

On This Page

Key Takeaways

  • Whitespace and type mismatches cause most lookup failures, so TRIM and type alignment come before any other formula.
  • XLOOKUP suits new sheets with its exact match default and built in not found value, while VLOOKUP remains valid for existing left key lookups.
  • REGEXREPLACE standardises phones and codes. QUERY deduplicates and filters in one step. IMPORTRANGE merges sheets without copy and paste.
  • Nested imports need one time access approval and run faster through a hidden cache tab.

Introduction

Quick Checklist

Step Action Why It Matters
1 Trim spaces and align text case and number types Lookups stop missing values that only differ by invisible characters
2 Standardise phones and codes with REGEX formulas One format per column makes grouping and matching reliable
3 Deduplicate and filter with UNIQUE or QUERY Analysis runs on one complete record per real world entity
4 Merge sheets with IMPORTRANGE and look up across them Regional or system exports become one live dataset
5 Validate totals and hand typo variants to fuzzy matching Formula work stays exact while near duplicates get proper scoring

Imported data rarely arrives ready for analysis. Names carry extra spaces. Emails mix upper and lower case. Dates arrive as text. Phone numbers mix dashes and brackets while regional sheets live in separate files.

This guide presents copy paste recipes for the three formula families that solve those problems directly in Google Sheets. REGEX functions standardise text. QUERY and UNIQUE deduplicate and filter. IMPORTRANGE with VLOOKUP and XLOOKUP merges and enriches across sheets. Each section works as a step by step tutorial and states the sample size behind it so results can be reproduced.

Prepare Text with TRIM and CLEAN Before Any Lookup

Extra spaces are the leading cause of lookup failure in the tested samples. A cell that displays as John Smith may contain a leading space or a trailing space or a non breaking space pasted from a web page.

Apply =TRIM(A2) to remove leading and trailing spaces and to collapse repeated internal spaces. Extend the recipe to =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) when data was copied from web pages, because TRIM alone does not catch the non breaking space character. Standardise case in the same pass with =PROPER(TRIM(LOWER(A2))) for names and with =ARRAYFORMULA(IF(C2:C="", "", LOWER(TRIM(C2:C)))) for email columns.

On a 500 row customer sample with seeded spacing faults, lookup success rose from 88 percent before trimming to 100 percent after trimming. Run this preparation on every text column before building any VLOOKUP or XLOOKUP on top of it. These habits form part of a wider routine described in Top 10 Data Cleaning Tips for Google Sheets.

VLOOKUP and XLOOKUP Formulas for Cleaned Data

VLOOKUP is the most searched lookup function in spreadsheets. XLOOKUP is the modern replacement that Google Sheets added for new work. Both return related values from a table and both fail on dirty input in identical ways.

A standard VLOOKUP names the key and the table and the column position, as in =VLOOKUP(A2, Products!A:C, 3, FALSE). The equivalent XLOOKUP names the key and the two ranges separately, as in =XLOOKUP(A2, Products!A:A, Products!C:C, "Not found"). XLOOKUP needs no column index. It searches in any direction. It defaults to exact matching and it carries its own not found value in the fourth argument.

When to Use Each Function

Situation Recommended Function Reason
Simple lookup with the key on the left VLOOKUP Short formula that every Sheets user recognises
Lookup to the left or with shifting columns XLOOKUP Range references stay valid when columns move
New sheet where errors must read cleanly XLOOKUP Built in not found value replaces extra error wrapping
Existing sheet with working formulas VLOOKUP Rewriting tested formulas adds risk without benefit
Filtering with aggregation in one step QUERY Lookup functions return rows while QUERY groups and sums

Type mismatches deserve special attention because they are invisible. A lookup key stored as text never equals the same digits stored as a number. Coerce the key so both sides share one type before comparing. See the troubleshooting section for the exact patterns.

For a deeper comparison of lookup behaviour against fuzzy approaches, read VLOOKUP vs Flookup Data Wrangler and XLOOKUP vs Flookup Data Wrangler.

Clean Phone Numbers and Codes with REGEXREPLACE

Google Sheets implements regular expressions through the RE2 engine. Three functions cover nearly all cleaning work.

Numeric cells need one precaution. REGEX functions accept text input, so a pure number cell should be coerced first with =REGEXREPLACE(A2&"", "[^0-9]+", ""). The empty string concatenation turns the value into text without changing its digits.

Phone standardisation then becomes a two stage recipe. First strip to digits, then rebuild one display format with a length check, as in =IF(LEN(REGEXREPLACE(D2,"\D",""))=10, "("&MID(REGEXREPLACE(D2,"\D",""),1,3)&") "&MID(REGEXREPLACE(D2,"\D",""),4,3)&"-"&MID(REGEXREPLACE(D2,"\D",""),7,4), D2). On a 400 row phone sample with six input variants, this recipe brought 392 rows into one format and left 8 genuinely short entries flagged for review.

International numbers need restraint. A single ten digit template cannot cover country codes and variable national lengths. Strip to digits with a country prefix preserved and route mixed country lists to a dedicated standardiser such as the workflow in Standardise Phone Numbers in Google Sheets.

Remove Duplicates and Filter with QUERY and UNIQUE

QUERY applies SQL style clauses to a range. It filters and deduplicates and aggregates in a single formula, which makes it the right choice when UNIQUE alone would need helper columns.

For a fast exact duplicate snapshot, =UNIQUE(A1:C10) returns each distinct row once. For deduplication combined with counting, =QUERY(A1:E12, "SELECT A, B, C, D, E, COUNT(A) WHERE A IS NOT NULL GROUP BY A, B, C, D, E", 1) groups identical rows and adds a count column that shows which entries were duplicated. The trailing 1 tells QUERY that the first row holds headers.

Two behaviours surprise first time users. QUERY sorts grouped output rather than preserving input order, so row order changes after grouping. QUERY inside an import also addresses columns by position rather than by letter, a pattern the combinations section explains in full.

On a 2,000 row order sample with seeded exact duplicates, UNIQUE and QUERY removed the same 214 duplicate rows. QUERY additionally returned per row counts that identified the three most repeated order identifiers without extra formulas. For a full comparison of built in removal options, read How to Remove Duplicates in Google Sheets. To highlight candidates visually before deleting them, read How to Find Duplicates with Conditional Formatting and COUNTIF in Google Sheets.

Combine Data from Multiple Sheets with IMPORTRANGE

IMPORTRANGE pulls a live range from one spreadsheet into another without copy and paste. Regional exports and system extracts can therefore feed one dashboard while each owner keeps editing the source file.

The function takes the source location and the range string, as in =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123xyz", "Sales!A1:F500"). The spreadsheet identifier alone may replace the full address for reliability. Viewer access to the source file is required before any data flows.

First time use shows a REF prompt that asks for connection approval. Open the cell and select Allow access. The import starts immediately after approval. Grant this approval with a lone IMPORTRANGE in a cell before nesting the call inside any other formula, because nested calls inherit the same permission requirement.

Combine QUERY with IMPORTRANGE and Look Up Across Sheets

Individual functions solve single sheet problems. Combined functions solve multi sheet workflows.

Bulk lookups benefit from one further wrapper. Wrapping the key range in ARRAYFORMULA fills results downward without dragging formulas. Constraining the output range keeps missing key rows from flooding the sheet with errors.

Fix QUERY, VLOOKUP and IMPORTRANGE Errors

Error Likely Cause Fix
REF on first IMPORTRANGE use Source sheet not yet authorised Run the IMPORTRANGE alone and select Allow access once per source
Persistent REF after approval Missing Viewer access or wrong tab name Confirm sharing rights and copy the tab name directly into the range string
VLOOKUP returns not available on matching values Text key against a numeric column or the reverse Coerce both sides to one type before comparing
QUERY rejects letter references in an import Letters do not exist on imported arrays Rewrite the clause with Col1 and Col2 positional references
Stacked import shows one region only Comma used where a semicolon belongs Join vertical ranges with a semicolon and skip the second header row
Grouped QUERY output changes row order Grouping sorts by design Add an explicit ORDER BY clause or sort after import

Type coercion deserves a concrete recipe because forum reports show it repeatedly. When a numeric identifier fails against text, test the text form and the numeric form in sequence so either storage type resolves. Helper columns with TEXT and VALUE make the coercion visible before the lookup runs.

Worked Example That Cleans a 500 Row Customer List

Consider a 500 row customer list split across two regional sheets with four faults. Names mix upper and lower case. Phone entries mix six formats. 40 rows repeat exactly while 25 near duplicate pairs differ by single character typos.

First the regions are stacked with one header row through the array pattern, which yields 500 rows in the working tab. TRIM and case standardisation then collapse spacing variants and the count of visibly distinct names falls by 18. REGEX stripping brings 392 of 400 phone entries into one ten digit format and the remaining 8 short entries move to a review list. QUERY grouping removes the 40 exact duplicates and returns counts that confirm the removal, which leaves 460 rows. VLOOKUP checks against the master product table then succeed on every key after type coercion, up from 44 failures before preparation.

Every stage is verifiable from row counts alone. The import count matches the sum of both regions. The trim stage changes only spacing variants. The REGEX stage reports its 8 exceptions while the QUERY counts prove that only true duplicates were removed. The 25 typo pairs remain and they belong to the fuzzy stage that exact formulas cannot decide.

Limits and When to Use Fuzzy Matching

Exact formulas stop at similarity. Jon Smith and John Smith share most characters yet no equality test joins them and no threshold argument exists on VLOOKUP or XLOOKUP to express closeness.

A threshold test on the remaining 25 typo pairs shows the boundary clearly. At 85 percent similarity, 21 pairs score as likely matches and 4 nickname style pairs fall below the line for manual review. That scored judgement is the work of fuzzy matching in Google Sheets, which returns a percentage per row instead of a binary hit or miss. For name heavy datasets, continue with the fuzzy name matching guide after the formula stages above are complete.

Final Thoughts

QUERY with REGEX and IMPORTRANGE covers the exact and structural half of data cleaning. Preparation removes invisible characters. REGEX enforces one format per column. QUERY deduplicates and filters in place. IMPORTRANGE with VLOOKUP and XLOOKUP turns scattered sheets into one live dataset. Measured counts at each stage keep the work trustworthy. To reuse these exact recipes under short names, read How to Create Named Functions in Google Sheets for Reusable Cleaning Formulas.

When the remaining rows differ by typos and abbreviations and word order, similarity scoring takes over. Flookup Data Wrangler adds that scoring inside Google Sheets with configurable thresholds and phonetic matching, so formula cleaning and fuzzy resolution form one pipeline. See the data cleaning tools for the full range.

You Might Also Like

How to Remove Duplicates in Google Sheets VLOOKUP vs Flookup Data Wrangler XLOOKUP vs Flookup Data Wrangler

Frequently Asked Questions

Should I use VLOOKUP or XLOOKUP for cleaning work?

Use XLOOKUP for new sheets because it defaults to exact matching and handles its own not found value. Keep VLOOKUP for existing sheets where the lookup column is on the left and the formulas already work.

Why does VLOOKUP miss when the value looks right?

The usual causes are extra spaces, non breaking spaces from web copy and paste and text versus number mismatches. Trim the columns first and coerce the key with TEXT or VALUE so both sides share one type.

Why does QUERY need Col1 notation inside IMPORTRANGE?

When QUERY receives an imported array it has no sheet letters to reference, so columns are addressed as Col1 and Col2 in order. Letter references fail in that position while Col notation filters and aggregates correctly.

Why does a nested IMPORTRANGE show a REF error?

The source sheet has not been authorised yet. Run the IMPORTRANGE alone in a cell first and select Allow access, then place it back inside VLOOKUP or QUERY.

How do I remove duplicates with QUERY or UNIQUE in Google Sheets?

Use UNIQUE for a fast exact duplicate snapshot of one range. Use QUERY when deduplication must happen together with filtering or aggregation in a single step.

How can I speed up dashboards that use IMPORTRANGE?

Import once into a hidden cache tab and point local QUERY and VLOOKUP formulas at that tab. The cache reduces repeated cross sheet recalculation on every dashboard refresh.