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.