Concat - remove dash if optional field left blank | Community
Skip to main content
TCarey
TCarey
Level 3
May 24, 2023
Solved

Concat - remove dash if optional field left blank

  • May 24, 2023
  • 1 reply
  • 1107 views

I am trying to remove dashes if an optional field is not filled out.  

 

Here is my current formula:

CONCAT({DE:Region - Requesting Business Area},"-",{DE:Primary Requesting Business Area},"-", {DE:Secondary Business Focus},"-",RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})

 

The first and third fields are optional (Region Requesting Business Area and Secondary Business Area) so I would like to remove the "-" if those fields are blank. 

 

I've tried to use the If IsBlank formula a few different ways and have not been successful. Can anyone help?  ty! 

 

Here's my last attempt: 

 

IF(ISBLANK({DE:Region - Requesting Business Area}," ","-",

IF(ISBLANK({DE:Primary Requesting Business Area}," ","-",

IF(ISBLANK({DE:Secondary Business Focus}," ","-",

CONCAT({DE:Region - Requesting Business Area},"-",{DE:Primary Requesting Business Area},"-", {DE:Secondary Business Focus},"-",RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})))))))

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by J_Mas

Hey @tcarey, give this a try:

 

displayname=My Calculated Field
textmode=true
valueexpression=CONCAT(IF(ISBLANK({DE:Region - Requesting Business Area}),"",CONCAT({DE:Region - Requesting Business Area}," - ")),{DE:Primary Requesting Business Area},IF(ISBLANK({DE:Secondary Business Focus}),"",CONCAT(" - ",{DE:Secondary Business Focus}))," - ",RIGHT(YEAR({entryDate}),2)," - ",{DE:Rally Feature Number}," - ",{DE:TruBrand Family})
valueformat=HTML

1 reply

J_Mas
J_Mas
Level 5
May 24, 2023

I often have trouble with ISBLANK depending on the type of field I'm using in the formula. What seems to work better for me is checking the length of the field. So something like:

IF(LEN({DE:Region - Requesting Business Area}>0,"-","")

So if there's any value in that field, it should get a -, otherwise it'll get nothing.

TCarey
TCareyAuthor
Level 3
May 31, 2023

thanks for your response!  However, I still can't seem to get it to work.  I'm sure it's how I'm phrasing it, I've tried a number of different ways and the system keeps highlighting IF LEN as errors. 

 

Concat all the chosen fields. If the length of a chosen field is less than 0 characters do not use a dash. 

 

CONCAT(IF(LEN({DE:Primary Requesting Business Area}>0,"-","",IF(LEN({DE:Secondary Business Focus}>0,"-","", RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})))))

 

IF(LEN({DE:Primary Requesting Business Area}>0,"-",""+

IF(LEN({DE:Secondary Business Focus}>0,"-",""+RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})))))

J_Mas
J_MasAccepted solution
Level 5
June 1, 2023

Hey @tcarey, give this a try:

 

displayname=My Calculated Field
textmode=true
valueexpression=CONCAT(IF(ISBLANK({DE:Region - Requesting Business Area}),"",CONCAT({DE:Region - Requesting Business Area}," - ")),{DE:Primary Requesting Business Area},IF(ISBLANK({DE:Secondary Business Focus}),"",CONCAT(" - ",{DE:Secondary Business Focus}))," - ",RIGHT(YEAR({entryDate}),2)," - ",{DE:Rally Feature Number}," - ",{DE:TruBrand Family})
valueformat=HTML

    Terms & ConditionsAccessibility statement

    Loading footer...