Teach Google Sheets to Clean Product Codes Without Formulas

The Problem: Product Code Chaos

Product codes are supposed to be a universal identifier. In theory, each product gets a unique code and that code remains consistent across your inventory, procurement and e-commerce systems. In practice, product codes become a nightmare.

One warehouse enters codes as PROD-2024-001 v2. Another uses prod_2024_001_v2. A third drops the version number entirely: PROD2024001. A fourth adds extra spaces: PROD - 2024 - 001. When these codes flow into your inventory system, financial reports or customer-facing catalogues, the mismatches create havoc.

Inventory queries fail to match the same product across systems. Reports show inflated duplicate counts. APIs reject malformed codes. Customer orders reference product codes that do not exist in your system. The damage is compounded because the errors are not obvious. Your data looks present and complete, but it is lying to you.

Most organisations solve this problem in one of three ways and all three are expensive.

Why Traditional Formulas Fail

Approach 1: Manual Cleanup involves assigning someone to review each malformed code and correct it by hand. This works for 100 codes. It scales terribly for 10,000 codes. The person doing the cleaning gets bored, makes mistakes and the problem resurfaces every time new data arrives.

Approach 2: Complex Formulas attempts to encode the transformation logic using nested IF statements or regular expressions. If your transformation is straightforward (remove dashes, convert to uppercase), this approach works. But product codes rarely have simple transformations. They combine multiple operations: remove dashes, remove version numbers, trim spaces, convert to uppercase and remove duplicate letters. Writing a formula that handles all these cases is not just complex, it becomes unmaintainable.

Approach 3: Custom Scripts requires hiring a developer to write Apps Script code or importing your data into a programming environment like Python. This works, but it is slow, expensive and breaks the workflow for analysts who just need to clean data in their spreadsheet.

All three approaches share a common weakness: they require you to anticipate every transformation rule upfront. The moment a new variation of the product code format appears, your formula breaks or your manual process stalls.

Introducing Learn from Examples

The Learn from Examples feature in Flookup Data Wrangler uses pattern synthesis to solve this problem differently. Instead of anticipating transformation rules, you simply show the system what you want. For a complete walkthrough of the feature, see the Learn from Examples documentation.

You provide a few examples of the dirty data and what the clean version should look like. The pattern synthesis engine analyses these examples, identifies the underlying transformation logic and applies that logic to your entire dataset. No formulas. No scripts. Just teach by example.

This approach has a critical advantage: it handles complexity naturally. When you show the system examples of a complex transformation, it learns all the component rules at once. Remove dashes, trim spaces, remove version numbers, convert to uppercase? The system figures it out from your examples.

Case Study: Product Identifiers at Marketplace Scale

Step 1: A Real Problem Worth Understanding

Amazon's 2024 Brand Protection Report documents the real scale of inconsistent product identifiers. Amazon scans billions of attempted product listings against its Brand Registry and the USPTO trademark registry and it states that it proactively blocked more than 99 percent of suspected infringing listings before a brand ever needed to report them. That linking only works because product identifiers written differently, for example AcmeCorp, Acme Corporation and Acme Corp., are recognised as the same underlying item.

Your product master data is a smaller version of the same problem. A Shopify product export, for example, can list the same item under several code formats. Each variant silently breaks downstream lookups and inventory reporting. Here is how you standardise it in Google Sheets. Open the Standardize text entries function from the Flookup Data Wrangler sidebar and choose the Learn from examples operation. No setup wizard. No configuration dialogs. Just a straightforward choice to teach by example.

Step 2: Gather Example Pairs

You create two columns in a dedicated sheet. The first column holds the dirty product codes exactly as they appear in your data. The second column holds the standardised version. A few examples are sufficient:

The examples show the system multiple variations of the same underlying rule: extract the product identifier, year and sequence number, then reformat as uppercase without separators or version information.

Step 3: Test the Pattern

You select both the dirty and clean example columns and click Test. The pattern synthesis engine displays the detected rule: Remove dashes, underscores and spaces. Remove version suffixes. Convert to uppercase. Reorder components to standard format.

A preview shows how the system will transform a sample of codes from your full dataset. The preview confirms the detected logic matches your intended transformation.

Step 4: Configure the Schedule

You select your full product code column as the input range and specify where the standardised codes should be written. Choose Process in a loop so that whenever new products are added to the inventory, the standardisation runs automatically.

Set the frequency to weekly and click Schedule.

Step 5: Monitor Results

The first run processes the full column and writes the standardised codes to your output range. Verify a sample of rows on the first execution, then leave the schedule in place so each weekly run cleans any new codes automatically.

How Pattern Synthesis Works

The Learn from Examples feature applies sophisticated analysis to your example pairs and identifies transformation rules. Here is what the system does under the hood:

1. Analyse the Differences: The system compares each dirty value to its clean counterpart and identifies what changed. Characters removed? Sequences reordered? Case converted?
2. Extract Common Patterns: It derives the rule from the transformations that every product code example has in common. If 90 percent of your examples remove dashes, that becomes part of the learned rule.
3. Build a Rule Pipeline: The system constructs a pipeline of primitive transformations (remove characters, collapse whitespace, change case, reorder sections) that, when applied in sequence, convert the dirty values to clean values.
4. Validate Against Examples: Before applying the rule to your full dataset, the system verifies that the discovered rule correctly transforms all your example pairs.
5. Apply to Full Dataset: Once validated, the rule is applied to every row in your input range.

The system can learn combinations of 24 primitive transformations across 6 categories: case operations, character removal, whitespace handling, punctuation treatment, bracket removal and section reordering. This flexibility allows it to handle nearly any consistent transformation you can demonstrate with examples.

What the Real Case Shows

The Amazon report also records the outcome of doing this at scale. In 2024 the approach identified, seized and disposed of more than 15 million counterfeit products worldwide and Amazon's Counterfeit Crimes Unit had pursued more than 24,000 bad actors since 2020. None of that linking would work if every variant of a product identifier had to be caught by hand.

Your own data is a smaller version of the same lesson. Once product codes are standardised, inventory queries match, API integrations accept the data and catalogue listings display cleanly. The work is a one-time teach-by-example step rather than a recurring manual task and a scheduled rule keeps new entries consistent as they arrive.

Getting Started with Your Own Data

Assess Your Transformation

Before you start, identify your dirty data and what the clean version should look like. Is it just removing special characters? Reordering sections? Converting case? Collapsing whitespace? The clearer you are about the transformation, the better your examples will be.

Create Example Pairs

In a new sheet, create two columns. Column A contains the dirty values, column B contains the clean versions. Aim for 3 to 5 examples that demonstrate the full range of variations in your data. If you have multiple distinct transformation patterns, you may need to create separate schedules for each pattern.

Test and Validate

Use the Test function in the scheduling sidebar to preview how the system will transform your data. If the detected rule matches your intention, proceed. If not, add another example pair that clarifies the rule and test again.

Schedule and Monitor

Once the rule is validated, select your full input range and configure the frequency. For ongoing data entry, choose Process in a loop so the rule runs continuously. For one-time cleanup, choose Process to end. Either way, monitor the first execution to verify results.

For scheduling instructions, see the Learn from Examples documentation.

Frequently Asked Questions

How many example pairs do I need?

Minimum is 2, but 3 to 5 examples typically produce the most robust rules. The more diverse your examples (showing different variations of the same underlying pattern), the better the system can generalise to your full dataset.

What if my data has multiple distinct transformation patterns?

If some codes follow one rule and others follow a different rule, you will need to create separate schedules for each pattern. Alternatively, you can pre-filter your data to group similar transformation patterns together and run multiple Learn from Examples operations sequentially.

Can it learn very complex transformations?

Yes, as long as the transformation is consistent across your examples. The system can handle combinations of case conversion, character removal, whitespace handling, punctuation removal and section reordering. If your transformation requires context beyond what appears in the individual cell (for example, looking up a value in another sheet), you will need a different approach.

What if the learned rule does not work correctly on my full dataset?

This usually means your examples did not capture the full range of variation in your data. Add more example pairs that show different edge cases, then test again. The learned rule will update based on the expanded set of examples.

Can I edit the learned rule manually?

No, but you do not need to. The rule is automatically re-derived from your examples every time you modify them. If you want to change the rule, add or modify example pairs to better reflect your desired transformation.