Hey Daniel, thanks for reaching out! Totally makes sense, creating a formula like this will require some verification system to determine whether the first normalization column did a 'good' job. Defining what a 'good' result versus 'poor' result looks like will be super important and a bit challenging due to the subjective nature of this task. Would also recommend this verification process is in a separate formula column to maintain an organized table.
I've tested this out with the following formula and it did a relatively good job. There are many variables you can adjust to restrict or loosen the requirements to determine if this cell did a 'good' job such as including specific characters, acronyms, or phrases you'd like to exclude.
AI Formula Column:
* 'Use AI' Prompt:
Create a formula that checks a box if {{normalized_name}} meets any of the following criteria:
<critera> (1) Contains non-standard elements such as ".com", ".be", ".co.uk", ".de", etc. (2) Includes an acronym (all uppercase letters and at least two characters, e.g., "ABC", "NZXT"). (3) Includes special characters or trademarks, such as "®", "™", or unusual punctuation (e.g., "!", "@"). Ignore "&". </critera>
Here's what to output: <output> The formula should return TRUE if any of these conditions are met, and FALSE otherwise. </output>
Example Cases: <examples> Input: "capellisport.com" → TRUE (contains ".com") Input: "NZXT" → TRUE (acronym) Input: "Tint World®" → TRUE (contains trademark symbol) Input: "Beliani" → FALSE (none of the criteria are met) </examples>
* Regex Formula:
/(\.com|\.be|\.co\.uk|\.de|[A-Z]{2,}|®|™|[^\w\s&])/g.test({{Normalize Company Name}}?.normalized_name)
This formula checks the output from the first attempt at normalizing a company name and uses the criteria above to make a decision. Since this is a formula, it is limited to binary criteria and won't be able to make a judgement or provide reasoning for why it made a decision like an AI column could.
Here are some of the results:
https://downloads.intercomcdn.com/i/o/w28k1kwz/1231388865/b8b449896826e7c7668d3a40661e/CleanShot+2024-10-28+at+10_17_14%402x.png?expires=1730126700&signature=b3e0b59b5fd26bd2c5952918891abf9175197119a567adfda26a26ca92a9a79a&req=dSIkF8p2lYlZXPMW1HO4zUtx1A1bEUf6TBm3HJPNqg4dD%2BtD8gbxQ0uc5wLI%0ACLBU%0A
Hope this helps! This formula will require some experimentation in which I recommend using ChatGPT or some sort of AI to help you design or execute on.
Let me know if you have any other questions! :)
.png){kind=logo}
