You need to sign in to do that
Don't have an account?
cldave
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 :)
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 :)
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
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