Substitute/replace picklist values in Formula field

Question

Company_Name__c &'_'& TEXT( GEO__c )&'_'& TEXT(Product__c) &'_'& TEXT(Year__c) 

I have a formula field as above where GEO__C, Product__C are picklist fields.

Now my requirement is Eg - GEO (India, America, Europe) and Product(Android Phone, Mac Phone)

When GEO is India and Product is Android Phone Value should be : (Samsung_IND_Android_Phone_2017) as per the above formula

GEO : India (IND), America (USA), Europe (UK) - it should substitute the value with upper case. Product : Android Phone(Picklist value should get converted to Android_phone)underscore should get added.

can someone help how to add substitute for picklist field

Answer 1

Salesforce has a Substitute function that you can use in formulas.

Its syntax like SUBSTITUTE(TEXT(GEO__c), "India", "IND"). You would need one of those substitutions for each value which will get cumbersome as you add values.

You could do the same with the Product field: SUBSTITUTE(TEXT(Product__c), " ", "_") would replace the space with an underscore.

Answer 2

I'm suggesting to use CASE formula to customize how you want you picklist values to be displayed:

SUBSTITUTE(
Company_Name__c & '_' &
CASE(TEXT( GEO__c ),
 'India', 'IND',
 'America', 'USA',
 'Europe', 'UK',
/* List any other known values */
 TEXT(GEO__c)
) & '_' & TEXT(Product__c) &'_'& TEXT(Year__c) 
, " ", "_")