CODEWITHSUNDEEP
excelexcel-formula
Allen Aguas
Allen Aguas

Reputation: 21

CONCATENATE to ignore blank results if one cell is missing

How can I 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.

=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."
)

Upvotes: 0

Views: 1197

Answers (3)

Allen Aguas
Allen Aguas

Reputation: 21

=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.")

Upvotes: 1

Gravitate
Gravitate

Reputation: 3064

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."
                )
            )
        )
    )
)

Upvotes: 0

5202456
5202456

Reputation: 962

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."))

Upvotes: 1

Related Questions