MS Excel Conditional CONCATENATE

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

• why on earth do you use CONCATENATE? Simply use `A1 & B1` – phuclv Jun 5 '17 at 5:48

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. • what's the point of `CONCATENATE(B2)`? – phuclv Jun 7 '17 at 9:13
• Yeah, that wasn't my best bit of programming logic... there were far more elegant answers after mine. – D3xbot Jun 21 '17 at 4:40

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!