How to Clean and Split Data in Google Sheets with AI

To clean and split messy data in Google Sheets with AI, highlight the range, open the Gemini side panel (or Data > Data cleanup > Cleanup suggestions), and describe what you want in plain English — Gemini can split inconsistent addresses, fix capitalization, and flag duplicates in one pass, while the built-in Split text to columns tool and SPLIT()/TEXTSPLIT() formulas still handle clean, single-delimiter data faster and for free. As of July 2026, which one you reach for depends on how messy your data actually is.

Gemini vs. Built-in Tools: Which Should You Use to Clean Data?

Use the built-in tools when your data follows one consistent rule (every entry uses the same comma, space, or dash). Reach for Gemini when the rule breaks down — mixed formats, missing pieces, or judgment calls a rigid delimiter can’t make. Both live inside Google Sheets; neither requires a third-party add-on.

Google Sheets now ships with three distinct paths for this kind of work, and they solve different problems:

Method	Best for	Cost (as of July 2026)	Key limit
Split text to columns	Clean data, one consistent delimiter	Free, built into every account	No AI — breaks on inconsistent formatting
SPLIT() / TEXTSPLIT()	Dynamic splits that update automatically	Free, built into every account	Formula-based; still needs a defined delimiter
Gemini side panel / Cleanup suggestions	Messy, inconsistent, or judgment-based cleanup	Included in Workspace Business Standard+ ($14/user/mo annual) or Google AI Plus+ (from $4.99/mo)	Sidebar-driven; suggestions need manual review before applying
=AI() function	Row-by-row transforms with a custom natural-language prompt	Same plans as above	Generates a max of 350 selected cells per batch; output can vary between runs

How to Split Messy Data with Gemini (Step by Step)

If your column mixes formats — some addresses have a suite number, some don’t; some names are “Last, First,” others are “First Last” — a single delimiter can’t cover every case. Gemini reads context instead of just splitting on a character.

1. Select the range you want to clean (e.g., column A with full addresses).
2. Open the Gemini icon in the top-right corner of Sheets, or go to Data > Data cleanup > Cleanup suggestions to let Sheets scan the range automatically.
3. In the Gemini panel, type a plain-language instruction, e.g. “Split this address column into Street, City, State, and ZIP in four new columns.”
4. Review the preview Gemini generates before accepting — it shows the proposed new columns without overwriting your source data.
5. Click Insert (or copy the output) to place the split columns next to your original range.
6. Spot-check 5–10 rows manually. Gemini is good at handling exceptions, but it isn’t infallible on ambiguous entries (e.g., a city name that could also be a street name).

This works well for one-off cleanups. If you need the same split to run automatically every time new rows are added, pair it with an Apps Script trigger — see our guide on automating Google Sheets with Apps Script and ChatGPT.

How to Split Clean Data with Text to Columns and SPLIT()

For data that already follows one rule, skip AI entirely — it’s slower than the native tool for this case. Text to Columns handles a one-time split in under 10 seconds.

1. Select the column with combined data.
2. Go to Data > Split text to columns.
3. Choose a separator: Comma, Semicolon, Period, Space, or Custom. Sheets also offers “Detect automatically.”
4. Sheets splits the values into adjacent columns instantly — no formula required.

If you’d rather keep the original column intact and generate the split as a live formula, use =SPLIT(A2, ",") or the newer =TEXTSPLIT(A2, ","), which supports multiple delimiters at once, e.g. =TEXTSPLIT(A2, ",", " "). Formulas recalculate automatically if the source cell changes — Text to Columns does not.

How to Remove Duplicates and Fix Formatting with AI

Deduplication and formatting cleanup (extra spaces, inconsistent capitalization, hidden characters) are the two most common “messy data” complaints, and Sheets now has a native menu for both.

Go to Data > Data cleanup and you’ll see two options: Remove duplicates, which lets you pick which columns count toward a match, and Cleanup suggestions, which is Gemini-powered and scans the whole sheet for trailing whitespace, mismatched date formats, and duplicate rows it thinks you missed. For a non-destructive alternative, the UNIQUE() function returns a new deduplicated list without touching your source data — useful if you want to compare before and after.

For formatting fixes across a whole column at once, TRIM() (removes extra spaces) and CLEAN() (strips non-printing characters) combined with ARRAYFORMULA still outperform AI for simple, rule-based fixes: =ARRAYFORMULA(TRIM(CLEAN(A2:A100))).

Can the =AI() Function Clean Data Too?

Yes — the =AI() function applies a custom prompt to each row individually, which makes it useful for cleanup tasks the side panel doesn’t cover, like standardizing free-text entries. But it comes with a hard ceiling worth knowing before you build a workflow around it.

As of July 2026, Google caps =AI() at generating results for the first 350 selected cells with AI formulas in one batch, and Google’s own documentation notes the same prompt can return slightly different wording on different runs — so it’s not the right tool for anything requiring exact, repeatable output (like a legal document ID). It also only reads what you explicitly pass in as an argument; it won’t scan the rest of your sheet for context on its own.

=AI("Standardize this company name to its most common short form: " & A2)

For row-by-row formula use, see our full walkthrough of the =AI() function in Google Sheets. If you’d rather write cleanup formulas with ChatGPT instead of Gemini, our guide on writing Google Sheets formulas with ChatGPT covers prompts for exactly that.

7 Copy-Paste Prompts for Cleaning and Splitting Data

Use these directly in the Gemini side panel (select your range first) or adapt them for the =AI() function. Each is written to describe the mess, not just the mechanical action — that’s what gets you a usable result on the first try.

Split the addresses in column A into separate Street, City, State, and ZIP columns. Some entries include an apartment or suite number in the middle of the street field — keep that with the street address.

Clean up column B: fix inconsistent capitalization so every name is in Title Case, and remove any leading or trailing spaces.

Look at columns A through D. Flag rows that are likely duplicate customer records even if the email or phone format doesn't match exactly (e.g., "555-123-4567" vs "5551234567").

Standardize every date in column C to MM/DD/YYYY format, including entries currently written as "March 3rd" or "3-3-26."

Extract just the domain name from each email address in column D (e.g., "acme.com" from "jane@acme.com") into a new column.

Split full names in column A into First Name and Last Name columns. If a name has a suffix like "Jr." or "III," keep it attached to the last name.

Scan column E for values that look like they should be numbers but are stored as text (extra symbols, spaces, or letters mixed in), and list which rows need manual review.

Which Method Should You Use? A Decision Framework

Choose Split text to columns if your delimiter is 100% consistent and you’re doing a one-time split. Choose SPLIT()/TEXTSPLIT() if you need the split to update live as source data changes. Choose Gemini’s side panel or Cleanup suggestions if the formatting is inconsistent, judgment calls are involved, or you’re deduplicating fuzzy matches. Choose =AI() if you need a custom, per-row transformation that a formula can’t express — but keep batches under 350 cells and don’t rely on it for anything requiring identical repeat output.

FAQ: Cleaning and Splitting Data in Google Sheets with AI

Does using Gemini to clean data in Sheets cost extra?
If your organization is on Google Workspace Business Standard or higher, Gemini features are included as of 2026 pricing (no separate per-feature charge). Personal Google accounts need a Google AI Plus, Pro, or Ultra subscription, starting at $4.99/month, to unlock Gemini in Sheets.

Is the =AI() function reliable enough for financial or legal data?
Not on its own. Google’s documentation confirms the same prompt can return slightly different output across runs, so treat =AI() results as a first draft to verify, not a final source of truth for anything requiring exact reproducibility.

Can I split or clean data in Google Sheets without any AI at all?
Yes. Split text to columns, SPLIT()/TEXTSPLIT(), UNIQUE(), and TRIM()/CLEAN() handle the majority of well-structured cleanup tasks for free and don’t require a Workspace AI plan.

Sources: Google Docs Editors Help – Use the AI function in Google Sheets, Google Docs Editors Help – Split text, remove duplicates, or trim whitespace.