Clay Icon

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.

  • Avatar of Kareem Amin
    Kareem Amin
    ·
    ·

    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

  • Avatar of Kareem Amin
    Kareem Amin
    ·
    ·

    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

  • Avatar of Kareem Amin
    Kareem Amin
    ·
    ·

    Eric E. Yash T. can we capture these normalizing and cleaning data use cases so we can make it more discoverable how to do this Cc: Luna

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·

    Words like: Limited, Consulting, etc.

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·

    Im not sure if the AI is or will be successful at what im asking

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·
  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    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!

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·

    That is the Use AI tab and it has not given the result I need.

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    Sebastian D. looking at the table

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·

    The formula seems to check out but the sample output doesn't

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    Sebastian D. sorry for the delay here - took a look this morning then got into meetings. looking again now.

  • Avatar of Yash T.
    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

  • Avatar of Yash T.
    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!

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·

    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()

  • Avatar of Sebastian D.
    Sebastian D.
    ·
    ·

    Thank you so much I was getting frustrated.

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    no worries, trying this right now

  • Avatar of Yash T.
    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.