MS Excel Conditional CONCATENATE

You don't need CONCATENATE.

Just use IF and ISBLANK

=IF(ISBLANK(B2), A2, B2)

When mobile is blank, it will take the value of tel, whatever the value is, including blank.

When mobile is not blank, it will take the value of mobile.


Excel allows the use of nested IF statements. Using IF, ISBLANK, and CONCATENATE, I was able to achieve the result you are after with the following formula:

=IF(ISBLANK(A2),CONCATENATE(B2),(IF(ISBLANK(B2),CONCATENATE(A2),CONCATENATE(B2))))

To display that in a more visually pleasing way, I've broken it down:

IF(ISBLANK(A2))
     CONCATENATE(B2)
ELSE IF(ISBLANK(B2))
     CONCATENATE(A2)
ELSE
     CONCATENATE(B2)

This may not be the prettiest code or logic, but it works.

Note: if both fields are empty, this formula will produce an empty cell. In the image below, I used the data from your question in columns A and B and the formula above created the output in column C. Row 5 was included in the screenshot because C5 contains the formula to show its output if A5 and B5 are blank.

Table output with input data from question and output data provided by the formula