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

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})
Sven-iX
Sven-iXAccepted solution
New Participant
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)
View reply

4 replies

Sven-iX
Sven-iXAccepted solution
New Participant
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)
Translate
    _Manish_Singh
    _Manish_Singh
    Level 9
    March 20, 2025

    Brilliant!

    Translate
      _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}, "")
      )

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



      Translate
      Terms & ConditionsAccessibility statement

      Loading footer...