CONCATENATE to ignore blank results if one cell is missing-CodePudding

essentially I want to do a Concatenate/Textjoin to output blank results if just one cell value is missing (I need all cell values to be present). This condition goes for N1388, P1388, K1388, and L1388.

Thanks in advanced!

=CONCATENATE( "Valued Customer, your Map(s) request is ready at this link. ", 
              "Please retrieve within 7 days. ",
              N1388, " ",
              "map(s) will be billed for ",
              P1388, ". "
              K1388, " ",
              L1388, ". ",
              "Please contact a service planner if necessary."
)

CodePudding user response：

This formula should do what you require, I have also tidied up some of the unnecessary text joins used for the spaces.

=IF(OR(ISBLANK(N1388),ISBLANK(P1388),ISBLANK(K1388),ISBLANK(L1388)),"",CONCATENATE("Valued Customer, your Map(s) request is ready at this link. Please retrieve within 7 days. ",N1388," map(s) will be billed for ",P1388,". ",K1388," ",L1388,". Please contact a service planner if necessary."))

CodePudding user response：

Similar to @5202456's answer, but mine will check for blanks which are a result of formulas. It is also slightly more efficient as the OR does not short circuit and all the conditions will be checked, even if the first one returns true.

=IF(N1388="",""
    IF(P1388="","",
        IF(K1388="","",
            IF(L1388="","",
                CONCATENATE( "Valued Customer, your Map(s) request is ready at this link. ", 
                              "Please retrieve within 7 days. ",
                              N1388, " ",
                              "map(s) will be billed for ",
                              P1388, ". "
                              K1388, " ",
                              L1388, ". ",
                              "Please contact a service planner if necessary."
                )
            )
        )
    )
)

CodePudding user response：

=IF(OR(N1388="",P1388="",K1388="",L1388=""),"",CONCATENATE("Valued Customer, your Map(s) request is ready at this link. Please retrieve within 7 days. ",N1388," map(s) will be billed for ",P1388,". ",K1388," ",L1388,". Please contact an service planner if necessary.")