.png&w=3840&q=75){kind=logo}
Column Data Cleaning: Add Words for Removal in Normalize Company Name
How do I remove a word or words from a column. I was looking in Normalize Company Name but I see that it removes LLC, Ltd, inc, etc. How do I add to this list of removables.
What are the other items you want to remove? You can use a formula and type in what you want in English and have the ai formula writer generate the formula for you
Alternatively if the cleaning of the name is complex you can use an AI column and give it examples of how you want to normalize
Words like: Limited, Consulting, etc.
Im not sure if the AI is or will be successful at what im asking
Hey Sebastian! For many edge cases that the formula may not be able to pick up here, the chatGPT integration called (Use AI) will likely have better reasoning and much more accurately achieve this for you. Kareem looped in the team in case they can help us add a few of these variations to the current normalization integration and perhaps do a better job overall. Appreciate you surfacing this!
That is the Use AI tab and it has not given the result I need.
Yash T.··Sebastian D. looking at the table
The formula seems to check out but the sample output doesn't
- Yash T.··
Sebastian D. sorry for the delay here - took a look this morning then got into meetings. looking again now.
- Yash T.··
Sebastian D. can you send me that formula you generated in that screenshot? it’s the right formula, but you’re using it in a conditional run, not the actual formula column
- Yash T.··
Sebastian D. I went ahead and added the regular Clay Normalize company name formula to your table, and a formula column that’s doing basic removal to the right. if you edit that formula column and copy paste the formula you have in the screenshot above, it will work!
Formula: {{Company Name}}?.replace(/(LLC|INC|CORP|LTD|CO|LP|LLP|PC|COMPANY|CORPORATION|LIMITED|PARTNERSHIP|ASSOCIATES|BUSINESS|GROUP|SERVICES|FIRM|AGENCY|OFFICE|CENTER|INSTITUTE|CLUB|SOCIETY|FOUNDATION|FUND|ENTERPRISES|INDUSTRIES|MANUFACTURING|TECHNOLOGIES|SYSTEMS|CONSULTING|STUDIO|WORKS|DESIGNS|PRODUCTIONS|PUBLICATIONS|ASSOCIATION|BUREAU|REFINERY|LABORATORIES|PHARMACEUTICALS|BANK|INSURANCE|TRUST|HOLDINGS|INVESTMENTS|CAPITAL|WALLET|RIA in a Box by COMPLY|FULLCIRCL)$/i, '').trim()
Thank you so much I was getting frustrated.
- Yash T.··
no worries, trying this right now
- Yash T.··
Sebastian D. just added and updated the formula. there might be a few more modifications to make, but just let us know! I made two separate columns since I noticed some emojis to remove as well, your cleaned names are in the Final Cleaned Name column! we’ll work on adding all this into the regular normalize name function asap too.