MS Excel Conditional CONCATENATE

Question

I have excel doc where I have two columns "Tel" and "Mobile". Some records have only Tel and some have only Mobile while some have both, Now I want a single column where:

  |     A     |     B     |     C
2 | 200000000 |           | HERE MUST BE 200000000
3 |           | 700000000 | HERE MUST BE 700000000
1 | 200000000 | 700000000 | HERE MUST BE 700000000

• If only Tel then put Tel

• If only Mobile then put Mobile

• If both then put Mobile

As you can see, the Mobile number is preferred over the landline Tel.

Now what query should I use? I used to do it with CONCATENATE(A1&B1) bu here if both columns have data I will get a non-sense number

Answer 1

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.

Answer 2

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.

Answer 3

I found a very neat way:

Go to Filter -> Uncheck All -> Select all Blank cells of Mobile (B) -> in the first cell put =A1 -> Enter -> Apply to all following cells -> remove he Filter!