I'm creating this formula field to concatenate the language and field value, if they do have any level of speaking that language. For example: "English - Fluent;Spanish - Proficient;Arabic - Beginner"
One thing I tried already is removing each line that excludes the N/A picklist value, because I could delete that value from the picklist, it's historical, but that only lowered the characters down to 7,163.
Any ideas? Formula below.
SUBSTITUTE(
SUBSTITUTE(
"."&
IF(NOT(ISPICKVAL(Language_Arabic__c,"")) && NOT(ISPICKVAL(Language_Arabic__c,"N/A"))
,"Arabic - "&TEXT(Language_Arabic__c)&";","")&
IF(NOT(ISPICKVAL(Language_Cantonese__c,"")) && NOT(ISPICKVAL(Language_Cantonese__c,"N/A"))
,"Cantonese - "&TEXT(Language_Cantonese__c)&";","")&
IF(NOT(ISPICKVAL(Language_English__c,"")) && NOT(ISPICKVAL(Language_English__c,"N/A"))
,"English - "&TEXT(Language_English__c)&";","")&
IF(NOT(ISPICKVAL(Language_Farsi_Persian__c,"")) && NOT(ISPICKVAL(Language_Farsi_Persian__c,"N/A"))
,"Farsi - "&TEXT(Language_Farsi_Persian__c)&";","")&
IF(NOT(ISPICKVAL(Language_Filipino_Tagalog__c,"")) && NOT(ISPICKVAL(Language_Filipino_Tagalog__c,"N/A"))
,"Filipino - "&TEXT(Language_Filipino_Tagalog__c)&";","")&
IF(NOT(ISPICKVAL(Language_French__c,"")) && NOT(ISPICKVAL(Language_French__c,"N/A"))
,"French - "&TEXT(Language_French__c)&";","")&
IF(NOT(ISPICKVAL(Language_German__c,"")) && NOT(ISPICKVAL(Language_German__c,"N/A"))
,"German - "&TEXT(Language_German__c)&";","")&
IF(NOT(ISPICKVAL(Language_Hindi__c,"")) && NOT(ISPICKVAL(Language_Hindi__c,"N/A"))
,"Hindi - "&TEXT(Language_Hindi__c)&";","")&
IF(NOT(ISPICKVAL(Language_Hmong__c,"")) && NOT(ISPICKVAL(Language_Hmong__c,"N/A"))
,"Hmong - "&TEXT(Language_Hmong__c)&";","")&
IF(NOT(ISPICKVAL(Language_Italian__c,"")) && NOT(ISPICKVAL(Language_Italian__c,"N/A"))
,"Italian - "&TEXT(Language_Italian__c)&";","")&
IF(NOT(ISPICKVAL(Language_Japanese__c,"")) && NOT(ISPICKVAL(Language_Japanese__c,"N/A"))
,"Japanese - "&TEXT(Language_Japanese__c)&";","")&
IF(NOT(ISPICKVAL(Language_Korean__c,"")) && NOT(ISPICKVAL(Language_Korean__c,"N/A"))
,"Korean - "&TEXT(Language_Korean__c)&";","")&
IF(NOT(ISPICKVAL(Language_Lao__c,"")) && NOT(ISPICKVAL(Language_Lao__c,"N/A"))
,"Lao - "&TEXT(Language_Lao__c)&";","")&
IF(NOT(ISPICKVAL(Language_Mandarin_Putonghua__c,"")) && NOT(ISPICKVAL(Language_Mandarin_Putonghua__c,"N/A"))
,"Mandarin - "&TEXT(Language_Mandarin_Putonghua__c)&";","")&
IF(NOT(ISPICKVAL(Language_Mien__c,"")) && NOT(ISPICKVAL(Language_Mien__c,"N/A"))
,"Mien - "&TEXT(Language_Mien__c)&";","")&
IF(NOT(ISPICKVAL(Language_Portuguese__c,"")) && NOT(ISPICKVAL(Language_Portuguese__c,"N/A"))
,"Portuguese - "&TEXT(Language_Portuguese__c)&";","")&
IF(NOT(ISPICKVAL(Language_Punjabi__c,"")) && NOT(ISPICKVAL(Language_Punjabi__c,"N/A"))
,"Punjabi - "&TEXT(Language_Punjabi__c)&";","")&
IF(NOT(ISPICKVAL(Language_Russian__c,"")) && NOT(ISPICKVAL(Language_Russian__c,"N/A"))
,"Russian - "&TEXT(Language_Russian__c)&";","")&
IF(NOT(ISPICKVAL(Language_Spanish__c,"")) && NOT(ISPICKVAL(Language_Spanish__c,"N/A"))
,"Spanish - "&TEXT(Language_Spanish__c)&";","")&
IF(NOT(ISPICKVAL(Language_Tongan__c,"")) && NOT(ISPICKVAL(Language_Tongan__c,"N/A"))
,"Tongan - "&TEXT(Language_Tongan__c)&";","")&
IF(NOT(ISPICKVAL(Language_Urdu__c,"")) && NOT(ISPICKVAL(Language_Urdu__c,"N/A"))
,"Urdu - "&TEXT(Language_Urdu__c)&";","")&
IF(NOT(ISPICKVAL(Language_Vietnamese__c,"")) && NOT(ISPICKVAL(Language_Vietnamese__c,"N/A"))
,"Vietnamese - "&TEXT(Language_Vietnamese__c)&";","")&
IF(NOT(ISPICKVAL(Language_Other__c,"")) && NOT(ISPICKVAL(Language_Other__c,"N/A"))
,"Other - "&TEXT(Language_Other__c)&";","")&".",
";.",""),
".","")
2 answers
Did you look into splitting the formula into 2 fields?
What to do if hitting this limit
The smaller character limit (3900) can be remedied by splitting the formula into 2 fields. Create another formula field and break out some part of the code into that one. Then call Formula_A within Formula_B. Each can have up to 3900 characters.
Here is the link.
https://help.salesforce.com/articleView?id=000324937&type=1&mode=1
