Automate Google Sheets Tasks You Used to Need Regex For

Why Regex Breaks in Spreadsheets

Regex is a pattern matcher. It does not understand meaning. It matches characters in a sequence, and that is all it does.

That works beautifully when your data is uniform. Real spreadsheet data is not uniform. It came from a CSV export, a webform, a sales rep typing on a phone, and a client who uses their own conventions. Five different formats in one column is normal.

A pattern matcher has no fallback for that. It matches or it fails. So you write branch after branch to cover each variation, and the formula becomes a small unreadable program living inside a cell.

The alternative is to describe the outcome instead of the pattern. That is what FITS does. It adds a =FITS() function to Google Sheets that takes an instruction in English. You say what you want out of the cell. The AI figures out how to get it.

1. Extraction: Pulling One Piece Out of a Cell

This is the biggest regex category. You have a full string and you need a fragment. A domain from a URL. A zip from an address. A first name from a full name.

The old way. Every fragment gets its own pattern, and every pattern has edge cases you did not think of.

That pulls a zip code. It also happily pulls a five digit street number if the zip is missing. Nothing warns you.

The FITS way. Name the thing you want.

The same shape works for anything else buried in the cell.

We have written the step-by-step version of the most common extraction jobs: extracting the domain from a URL, separating first and last names, and pulling data from a website into Sheets.

2. Cleanup: Making Messy Data Consistent

Cleanup is where people lose whole afternoons. Phone numbers in six formats. Dates as text. Stray emojis. Double spaces. Names in RANDOM caps.

The old way. Standardizing a phone column means stripping every non-digit, then rebuilding the string by position.

Four REGEXREPLACE calls on the same cell. Add a country code and it collapses.

The FITS way. State the format you want.

The same approach handles the rest of the cleanup pile. Mixed date formats, special characters, inconsistent casing, all of it.

The key difference is tolerance. Regex fails on the row it did not expect. A plain-English instruction handles the outlier the same way a human would, by reading it and doing the sensible thing. Our guide to messy data extraction goes deeper on the cleanup workflow.

3. Text Manipulation: Rewriting What Is Already There

Sometimes you are not pulling data out. You are reshaping it. Cut everything after the @. Merge three columns. Remove one word from every row. Count how many times something appears.

The old way. Counting occurrences of a word in a range has no native function. So people use a SUMPRODUCT and SUBSTITUTE trick that measures how much shorter the text gets when you delete the word.

It works. It also takes ten minutes to explain, and nobody remembers why the division is there.

The FITS way. Ask for the count.

Reshaping works the same way. You describe the edit rather than composing it out of LEFT, FIND, and SUBSTITUTE.

The full walkthrough is in how to count specific text in Google Sheets.

4. Categorization: The Thing Regex Simply Cannot Do

Here is where the comparison stops being fair.

You have 800 rows of customer feedback. You want each one tagged positive, negative, or neutral. Or you have a year of transactions and you want them sorted into expense categories. Or you have support tickets and you want the topic of each one.

The old way. There is no old way. Regex matches characters, so the best you can do is a keyword list, and a keyword list is a bad proxy for meaning.

Feed it "not great, honestly" and it returns Positive. The pattern saw the word. It could not see the sentence.

The FITS way. This is the category where AI in the cell is not a shortcut, it is the only option.

Auto-tagging, keyword extraction, grouping similar rows, topic labelling. None of these have a native formula. They all take one line of FITS. If you want more ideas in this shape, see our AI formulas for content marketers collection.

But What About Smart Fill?

Fair question, and almost nobody writing about regex alternatives brings it up.

Google Sheets has a built-in machine learning feature called Smart Fill. You type one or two examples of the output you want in the next column. Sheets spots the pattern and offers to fill the rest. Press Tab and it does. No formula, no regex.

It is genuinely good, and you should use it. For simple, visually obvious extractions (first names out of emails, domains out of tidy URLs) it is the fastest path there is.

Here is where it stops. Smart Fill infers a pattern from your examples, so it has the same core limit as regex. It sees structure, not meaning.

• It cannot judge sentiment or categorize a transaction. There is no pattern to infer.
• It gets unreliable when the column has several competing formats.
• It gives you static pasted values, not a formula that updates when the source changes.
• It cannot fix what is actually wrong. It only reformats what is there.

That last one is the real gap. Say a row contains an email at "gamil.com". Regex will happily extract the typo. Smart Fill will happily copy the typo. Both did their job correctly. You still have bad data.

Only a tool that understands what an email is supposed to look like can catch that. Pattern matchers cannot, by definition.

How to Set This Up (Step by Step)

1. Install FITS from the Google Workspace Marketplace. It takes about thirty seconds.
2. Open any Google Sheet. FITS appears under the Extensions menu.
3. Add your API key (Gemini, OpenAI, or Claude). The getting started guide walks through it.
4. Type =FITS() in any cell, describe what you want, and reference the cell.
5. Drag the formula down the column like any other formula.

You can also pick the model and tune how creative the output is. Lower temperature means more predictable output, which is what you want for data work.

The free tier covers everyday use. If you are running formulas across thousands of rows, FITS Premium lifts the limits and unlocks the higher-end models.

Which Tool for Which Job

Being honest about this matters, because the wrong tool wastes your time either way. Regex is not the enemy. Using it for jobs it was never built for is.

Keep regex for uniform input and trivial patterns. It runs locally, it costs nothing, and it is fast. Reach for =FITS() when the data is human-entered, when formats vary, when the rule is easier to say than to encode, or when the job needs actual comprehension. That covers most real spreadsheets most of the time.

The Short Version

Regex asks you to describe the shape of your data. That only works if you already know every shape it will take. You never do.

Plain-English formulas ask you to describe the result instead. You always know that. It is why you opened the spreadsheet in the first place.