Learn from Examples in Google Sheets
Quick Start
- Prepare examples. Create two columns: dirty values | clean versions. Start with 3-5 pairs. → Full guide
- Open the scene. Extensions > Flookup Data Wrangler > Data Processing Functions. Click Standardize Data.
- Select the feature. Change the Type dropdown to Learn from examples.
- Fill in ranges. Input range (your data), Dirty values range, Clean values range. Use Grab selected range or type manually.
- Test & Run. Click Test pattern, review the preview, then click Run.
How Pattern Synthesis Works
From your perspective, here is what happens:
- Compares each pair. Notes what changed between the dirty and clean value in every row. Dashes removed? Case changed? Each difference is recorded.
- Finds the pattern. Identifies what is consistent across all your pairs. If every pair removes dashes, that becomes part of the rule.
- Builds one rule. Combines all changes into a single transformation.
- Shows a summary. The preview displays the rule in plain English (e.g. Remove dashes, underscores and spaces. Remove version suffixes. Convert to uppercase.). This is your chance to verify the logic.
- Applies it. Click Run and every row in your Input range is transformed.
Change the examples to change the rule. You never edit it directly.
1. Prepare Example Pairs
Create two columns anywhere in your workbook. Column A holds the messy values as they appear in your data. Column B holds the clean versions you want the feature to produce.
Example pairs for standardising product codes:
| Dirty (how it appears) | Clean (what it should become) |
|---|---|
PROD-2024-001 v2 |
PROD2024001 |
prod_2024_001_v2 |
PROD2024001 |
PROD - 2024 - 001 |
PROD2024001 |
How many pairs you need determines how well the results match your data:
| Quality | What That Looks Like | Result |
|---|---|---|
| Good | 5-10 pairs covering every messy format. Each change type appears in multiple pairs. | Accurate standardisation in one pass. |
| Marginal | 3-5 pairs with limited variation. Some patterns shown only once. | Most rows handled, but edge cases slip through. Add more examples. |
| Poor | 1-2 pairs that do not represent your data's variety. | Unreliable results. The feature cannot infer the complete rule. |
2. Fill in the Ranges
Three fields appear when you select Learn from examples. Click a cell in your sheet and use Grab selected range to populate each field, or type the range manually (e.g. 'Sheet1'!A1:A100):
- Input range. The column with the messy data you want to clean.
- Dirty values range. Your example dirty values.
- Clean values range. The corresponding clean versions.
3. Test and Run
Click Test pattern. The sidebar shows a preview table with three columns: Dirty, Expected and Result.
- Green match (Result = Expected). The pattern works for that row.
- Red mismatch (Result ≠ Expected). The pattern missed something. Add more example pairs that cover the problematic case, then test again.
A label beneath the preview summarises the detected rule. If the rule does not match your intention, your examples are too few or too similar. Add variety and test again.
The preview does not change your data. You can test as many times as needed.
Once the preview looks correct, click Run. The feature processes every row in your Input range and writes the standardised values starting at the top-left cell of your selected range.
Best Practices
- Show every messy format at least once. The feature can only learn patterns it has seen.
- Keep clean values consistent. Every clean value must follow the same target format. Inconsistencies confuse the rule.
- Include edge cases from the start. Unusual spacing, mixed case, special characters. Add them early to avoid fixing them after the first run.
- Plan for 2-3 rounds of refinement. First pass catches common patterns. Subsequent passes handle edge cases.
Frequently Asked Questions
How many example pairs do I need?
Start with 3-5. If the test preview misses something, add more until every pattern is covered. Simple transformations need fewer examples. Complex ones may need up to 15.
Does it work with numbers and dates?
Yes. Dates, currencies, measurement units. Any formatted data works. Show the feature the messy version and the clean version.
Can I save examples for reuse?
Data Nova users can save patterns to their Flookup account. Saved patterns appear in the Standardize Data scene for one-click reuse on recurring tasks.
What if the preview shows mismatches?
Your examples do not fully explain the rule. Add more pairs covering the missed cases and test again. The rule updates automatically from the expanded set.
Can I edit the rule manually?
No. Change the examples instead. The rule is re-derived from them every time.