1

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

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

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.

1

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

  • 1
    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
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.