Skip to main content
OmahaOmaha
Level 4
March 19, 2025
Répondu

Removing a comma on my calculated field

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})
Meilleure réponse par 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 commentaires

_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 )



Sven-iX
Community Advisor
Sven-iXCommunity AdvisorRéponse
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!

      Conditions d'utilisationAccessibility statement

      Loading footer...