Removing a comma on my calculated field | Community
Skip to main content
O
OmahaOmaha
Level 4
March 19, 2025
Solved

Removing a comma on my calculated field

  • March 19, 2025
  • 4 replies
  • 572 views

Hi All, I've created the following calculated field that works lovely except for the fact that when the first field in the calculation is empty, the field displays a comma before any of the following fields. Not a big deal but kind of an eyesore. How would I go about removing that first comma from the following calculation if the first field is blank?

 

CONCAT({DE:CL 1:name},IF(ISBLANK({DE:CL 2:name}),"", ", "),{DE:CCL 2:name},IF(ISBLANK({DE:CL 3:name}),"", ", "),{DE:CL 3:name},IF(ISBLANK({DE:CUL 1:name}),"", ", "),{DE:CUL 1:name},IF(ISBLANK({DE:CUL 2:name}),"", ", "),{DE:CUL 2:name},IF(ISBLANK({DE:CUL 3:name}),"", ", "),{DE:CUL 3:name},IF(ISBLANK({DE:PL 1:name}),"", ", "),{DE:PL 1:name},IF(ISBLANK({DE:PL 2:name}),"", ", "),{DE:PL 2:name},IF(ISBLANK({DE:PL 3:name}),"", ", "),{DE:PL 3:name})
Best answer by Sven-iX

I would simplify a little and then remove the first char. 
Note that i ALWAYS have a comma followed by a value - or nothing. That means I can safely remove the first char (which will always be a comma) 

SUBSTR(IF(ISBLANK({DE:CL 1:name}),"",","+{DE:CL 1:name})+ IF(ISBLANK({DE:CL 2:name}),"",","+{DE:CL 2:name})+ IF(ISBLANK({DE:CL 3:name}),"",","+{DE:CL 3:name})+ IF(ISBLANK({DE:CUL 1:name}),"",","+{DE:CUL 1:name})+ IF(ISBLANK({DE:CUL 2:name}),"",","+{DE:CUL 2:name})+ IF(ISBLANK({DE:CUL 3:name}),"",","+{DE:CUL 3:name})+ IF(ISBLANK({DE:PL 1:name}),"",","+{DE:PL 1:name})+ IF(ISBLANK({DE:PL 2:name}),"",","+{DE:PL 2:name})+ IF(ISBLANK({DE:PL 3:name}),"",","+{DE:PL 3:name}),1)

4 replies

_Manish_Singh
_Manish_Singh
Level 9
March 20, 2025

Hi @omahaomaha - Could you try this? I'm not sure if there is a better and easy way to do this which is manageable.

CONCAT(
IF(!ISBLANK({DE:CL 1:name}), {DE:CL 1:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name})) & !ISBLANK({DE:CL 2:name}), ", ", ""),
IF(!ISBLANK({DE:CL 2:name}), {DE:CL 2:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name})) & !ISBLANK({DE:CL 3:name}), ", ", ""),
IF(!ISBLANK({DE:CL 3:name}), {DE:CL 3:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name}, {DE:CL 3:name})) & !ISBLANK({DE:CUL 1:name}), ", ", ""),
IF(!ISBLANK({DE:CUL 1:name}), {DE:CUL 1:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name}, {DE:CL 3:name}, {DE:CUL 1:name})) & !ISBLANK({DE:CUL 2:name}), ", ", ""),
IF(!ISBLANK({DE:CUL 2:name}), {DE:CUL 2:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name}, {DE:CL 3:name}, {DE:CUL 1:name}, {DE:CUL 2:name})) & !ISBLANK({DE:CUL 3:name}), ", ", ""),
IF(!ISBLANK({DE:CUL 3:name}), {DE:CUL 3:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name}, {DE:CL 3:name}, {DE:CUL 1:name}, {DE:CUL 2:name}, {DE:CUL 3:name})) & !ISBLANK({DE:PL 1:name}), ", ", ""),
IF(!ISBLANK({DE:PL 1:name}), {DE:PL 1:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name}, {DE:CL 3:name}, {DE:CUL 1:name}, {DE:CUL 2:name}, {DE:CUL 3:name}, {DE:PL 1:name})) & !ISBLANK({DE:PL 2:name}), ", ", ""),
IF(!ISBLANK({DE:PL 2:name}), {DE:PL 2:name}, ""),
IF(!ISBLANK(CONCAT({DE:CL 1:name}, {DE:CL 2:name}, {DE:CL 3:name}, {DE:CUL 1:name}, {DE:CUL 2:name}, {DE:CUL 3:name}, {DE:PL 1:name}, {DE:PL 2:name})) & !ISBLANK({DE:PL 3:name}), ", ", ""),
IF(!ISBLANK({DE:PL 3:name}), {DE:PL 3:name}, "")
)

_Manish_Singh
_Manish_Singh
Level 9
March 20, 2025

Please try this as well. This code is more manageable.

LEFT( CONCAT( IF(ISBLANK({DE:CL 1:name}), "", CONCAT({DE:CL 1:name}, ", ")), IF(ISBLANK({DE:CL 2:name}), "", CONCAT({DE:CL 2:name}, ", ")), IF(ISBLANK({DE:CL 3:name}), "", CONCAT({DE:CL 3:name}, ", ")), IF(ISBLANK({DE:CUL 1:name}), "", CONCAT({DE:CUL 1:name}, ", ")), IF(ISBLANK({DE:CUL 2:name}), "", CONCAT({DE:CUL 2:name}, ", ")), IF(ISBLANK({DE:CUL 3:name}), "", CONCAT({DE:CUL 3:name}, ", ")), IF(ISBLANK({DE:PL 1:name}), "", CONCAT({DE:PL 1:name}, ", ")), IF(ISBLANK({DE:PL 2:name}), "", CONCAT({DE:PL 2:name}, ", ")), IF(ISBLANK({DE:PL 3:name}), "", CONCAT({DE:PL 3:name}, ", ")) ), LEN( CONCAT( IF(ISBLANK({DE:CL 1:name}), "", CONCAT({DE:CL 1:name}, ", ")), IF(ISBLANK({DE:CL 2:name}), "", CONCAT({DE:CL 2:name}, ", ")), IF(ISBLANK({DE:CL 3:name}), "", CONCAT({DE:CL 3:name}, ", ")), IF(ISBLANK({DE:CUL 1:name}), "", CONCAT({DE:CUL 1:name}, ", ")), IF(ISBLANK({DE:CUL 2:name}), "", CONCAT({DE:CUL 2:name}, ", ")), IF(ISBLANK({DE:CUL 3:name}), "", CONCAT({DE:CUL 3:name}, ", ")), IF(ISBLANK({DE:PL 1:name}), "", CONCAT({DE:PL 1:name}, ", ")), IF(ISBLANK({DE:PL 2:name}), "", CONCAT({DE:PL 2:name}, ", ")), IF(ISBLANK({DE:PL 3:name}), "", CONCAT({DE:PL 3:name}, ", ")) ) ) - 2 )



0
Sven-iX
Community Advisor
Sven-iXCommunity AdvisorAccepted solution
Community Advisor
March 20, 2025

I would simplify a little and then remove the first char. 
Note that i ALWAYS have a comma followed by a value - or nothing. That means I can safely remove the first char (which will always be a comma) 

SUBSTR(IF(ISBLANK({DE:CL 1:name}),"",","+{DE:CL 1:name})+ IF(ISBLANK({DE:CL 2:name}),"",","+{DE:CL 2:name})+ IF(ISBLANK({DE:CL 3:name}),"",","+{DE:CL 3:name})+ IF(ISBLANK({DE:CUL 1:name}),"",","+{DE:CUL 1:name})+ IF(ISBLANK({DE:CUL 2:name}),"",","+{DE:CUL 2:name})+ IF(ISBLANK({DE:CUL 3:name}),"",","+{DE:CUL 3:name})+ IF(ISBLANK({DE:PL 1:name}),"",","+{DE:PL 1:name})+ IF(ISBLANK({DE:PL 2:name}),"",","+{DE:PL 2:name})+ IF(ISBLANK({DE:PL 3:name}),"",","+{DE:PL 3:name}),1)
    _Manish_Singh
    _Manish_Singh
    Level 9
    March 20, 2025

    Brilliant!

    0
      Terms & ConditionsAccessibility statement

      Loading footer...