function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
cldavecldave 

Help with a formula field with a conditional substitute

Hi ,

I have a formula that connects multiple strings if conditions are met. I need to have a comma separating each string, but want to make sure it does not end with a comma (,)

Here's my formula

If(OR(NOT(ISBLANK( Signatures_Initials_missing_on_page__c )),NOT(ISBLANK( Missing_Pages__c )),NOT(ISBLANK( Document_Expired_on__c )),NOT(ISBLANK( Other_issues__c )), NOT(ISBLANK( Unclear1__c ))),"Please resend, the item received is", Null) +
If(  Is_Item_Not_Clear__c =true," not clear -" & Unclear1__c & " - ",null) +
If( NOT(ISBLANK( Missing_Pages__c)) ," missing the following  page(s): " & Missing_Pages__c & ",  " ,null) +
If( NOT(ISBLANK(  Signatures_Initials_missing_on_page__c )) ,"  missing signature(s) and/or initials on the following page(s): " & Signatures_Initials_missing_on_page__c & ", ",null) +
If( NOT(ISBLANK(   Document_Expired_on__c  )) ," expired since: " & (TEXT(MONTH( Document_Expired_on__c ))& "/" & TEXT(DAY(Document_Expired_on__c))& "/" & TEXT(YEAR(Document_Expired_on__c)))& ", " ,null)+
If( NOT(ISBLANK(    Other_issues__c  )) , Other_issues__c & ", " ,null)

Is there any way to add to it that if last character contain a comma, to remove it (substitute with blank?)


Thank you in advance :)

 
Best Answer chosen by cldave
William TranWilliam Tran
If you know there will always be a comma at the end you can use this:

LEFT(YOURCODE, LEN(YOURCODE)-1)

but if you don't know then use it:

IF(RIGHT(YOURCODE, 1)=',', LEFT(YOURCODE, LEN(YOURCODE)-1), YOURCODE) 

if it's too long, you may consider 2 formulas, one for your code and one to remove the end comma.

Thx

All Answers

William TranWilliam Tran
If you know there will always be a comma at the end you can use this:

LEFT(YOURCODE, LEN(YOURCODE)-1)

but if you don't know then use it:

IF(RIGHT(YOURCODE, 1)=',', LEFT(YOURCODE, LEN(YOURCODE)-1), YOURCODE) 

if it's too long, you may consider 2 formulas, one for your code and one to remove the end comma.

Thx
This was selected as the best answer
cldavecldave
Thank you very much , I used the first one and worked great!
Krishna DambirKrishna Dambir
hi i have same issue , i am updating a field on user with queue names, after every queue name i can put comma but it applies for last queue name also . how can i replace it with full stop after last queue name?