Want to merge two column that contains mobile number into one based on the condition that mobile no should be 10 digits

Question

I have two contacts with no column

contact no      contact no 1
9864573828       0145883
9834765849       1923832
018294           9876547834

I want to merge two columns into 1 having a contact no of 10 digits.

contact no      
9864573828       
9834765849       
9876547834       

I'm using Excel 2013

Answer 1

In Excel 2013 this formula can be used to list the 10 digit numbers from the first and second range without gaps:

=IFERROR(IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A:A)/(LEN(A:A)=10)/(ISNUMBER(--A:A)),ROW(1:1))),INDEX(B:B,AGGREGATE(15,6,ROW(B:B)/(LEN(B:B)=10)/(ISNUMBER(--B:B)),ROW(1:1)-SUMPRODUCT((LEN(A:A)=10)*(ISNUMBER(--A:A)))))),"")

It uses a lot of resources to calculate, so whole column references is highly discouraged. So use actual ranges instead like:

=IFERROR(
   IFERROR(
     INDEX(A:A,
           AGGREGATE(15,6,
                     ROW($A$2:$A$5)
                     /(LEN($A$2:$A$5)=10)
                     /(ISNUMBER(--$A$2:$A$5)),
                     ROW(1:1))),
     INDEX(B:B,
           AGGREGATE(15,6,
                     ROW($B$2:$B$5)
                     /(LEN($B$2:$B$5)=10)
                     /(ISNUMBER(--$B$2:$B$5)),
                     ROW(1:1)
                     -SUMPRODUCT(
                         (LEN($A$2:$A$5)=10)
                         *(ISNUMBER(--$A$2:$A$5)))))),
   "")

Note: I think (unable to verify myself) the formula needs entered with ctrl+shift+enter to make it an array formula.

This formula obtains the first row of the first range when the length of the string is 10 and the conversion of the string to a number does not result in an error (what would happen in case of text characters in the string).

When you drag the formula down, it displays the second value found, then the third, and so on, until no more values are found in the first range.

The IFERROR then directs it to search for the same reasoning in the second range.

We use the same counter and SUMPRODUCT to deduct the entire number of strings that satisfy the criteria in the first range since we want it to display the first found value first and we are unable to reset the ROW(1:1)*, which serves as a counter for the first smallest, second smallest, etc. In this manner, the counter will begin counting at 1 for the second range.

The second range will display a blank value if no additional data can be discovered there. To see each result, drag the formula down to the first empty result.

With genuine range references, it's probably still sluggish. I strongly suggest upgrading to Office 365.