2. Concatenate



 PROBLEM:  How to concatenate code, description and batch in one sentence?


=2.IF([@Type]<>"";"";IF([@√]="√";"";IF([@CODE]="";"";1.CONCATENATE([@CODE];" - ";[@DESCRIPTION];", batch: ";[@BATCH]))))
  1.  CONCATENATE cell form columns ([@CODE],[@DESCRIPTION] and [@BATCH]). Between them included some delimiters like " - " and ", batch: ";


  2.   To clean the table up! We want values that have [@Type], [@√] or [@CODE] different from  " " .


    This formula is a nested IF, OR, and CONCATENATE function that concatenates the values in the columns CODE, DESCRIPTION, and BATCH of the current row of the table where the value in the column Type is empty, the value in the column √ is equal to √, and the value in the column CODEis not empty. If any of these conditions are not met, it returns an empty string.

    Here’s how the formula works:

  1.  [@Type]<>"" returns TRUE if the value in the current row of the column Type is not empty.

  2.  [@√]="√" returns TRUE if the value in the current row of the column √ is equal to √.

  3.  [@CODE]<>"" returns TRUE if the value in the current row of the column CODE is not empty.

  4.  OR(...) returns TRUE if any of the conditions in steps 1-3 are TRUE.

  5.  CONCATENATE(...) concatenates the values in the columns CODE, DESCRIPTION, and BATCH of the current row of the table where the value in the column Type is empty, the value in the column √ is equal to √, and the value in the column CODE is not empty.

  6.  IF(...) returns the result of step 5 if the condition in step 4 is FALSE. Otherwise, it returns an empty string.

CONCATENATE

Concatenate - Syntax

CONCATENATE(text1, [text2], ...)

For example: The result for the first cell:
"17 - GDNS, batch: 6"