Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jack VolkovJack Volkov 

Clean Company Name Field Formula

Sharing this in case anyone else needs it.  Here is a formula field for clean company name.  
TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Company, 
",", " ") /*remove commas*/,
".", " ") /*remove periods*/,
" inc", "") /*remove inc*/,
" Inc", "") /*remove Inc*/,
" INC", "") /*remove INC*/,
" llc", "") /*remove llc*/,
" Llc", "") /*remove Llc*/,
" LLC", "") /*remove LLC*/,
" Ltd", "") /*remove Ltd*/,
" LTD", "") /*remove LTD*/,
"Corporation", "") /*remove Corporation*/
)

If you have an improved version please post :)
Alper Tunga GülbaharAlper Tunga Gülbahar
TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(Company)," INC", "")," LLC", ""), " LTD", ""),",",""),".",""))
This seems shorter if you don't need the case sensitive name of the company.
 
Gary SalvadorGary Salvador
Where and how did you use this?
Gary SalvadorGary Salvador
Hi Jack, Where did you put this formula?
 
Carrie Nunemaker 13Carrie Nunemaker 13
This was amazing to find! Thank you for posting it. 
Jack VolkovJack Volkov
Elliot MacNeille on our team discovered Salesforce formulas - at least ‘CONTAINS()’ function - may be case sensitive. Even if they are not some substitutes can be saved by leveraging ’LOWER()’
Sean ArnoldSean Arnold
@Gary
I'm going to use this on the Lead object to search for similar Account names and minimize manual routing of Leads for existing accounts
Victoria PhamVictoria Pham
This formula worked beautifully except... we have a small subset of companies who have the word "Incorporated" at the end of the company name. 

When we use this formula, it removes the "Inc" from "Incorporated" but not "corporated" ... leaving some company names showing up as (for example) "Account Namecorporated" or "My company namecorporated" 

I tried to add a new line to remove the word "Incorporated" (see below) but it doesn't seem to be working??

Suggestions?

TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Name , 
",", " ") /*remove commas*/,
".", " ") /*remove periods*/,
" inc", "") /*remove inc*/,
" Inc", "") /*remove Inc*/,
" INC", "") /*remove INC*/,
" llc", "") /*remove llc*/,
" Llc", "") /*remove Llc*/,
" LLC", "") /*remove LLC*/,
" Ltd", "") /*remove Ltd*/,
" LTD", "") /*remove LTD*/,
"Incorporated", "") /*remove Incorporated*/,
"Corporation", "") /*remove Corporation*/
)
Victoria PhamVictoria Pham
^ correction to post above, company names show up with "orporated" not "corporated" at the end.
Victoria PhamVictoria Pham
Nevermind... Figured out the right formula.


TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( Name , 
",", " "),
".", " "),
" inc", ""),
" Incorporated", ""),
" Inc", ""),
" INC", ""),
" llc", ""),
" Llc", ""),
" LLC", ""),
" Ltd", ""),
" LTD", ""),
"Corporation", ""))