want to merge two column that contains mobile number into one based on the condition that mobile no-CodePudding

I am new to excel please help me.

So the situation is we have two contact no column

contact no      contact no 1
9864573828       0145883
9834765849       1923832
018294           9876547834

i want to merge two column into 1 having contact no of 10 digit.

I'm using Excel 2013

CodePudding user response：

Try the following formula-

=LET(x,TOCOL(A2:B13,1),FILTER(x,LEN(x)=10))

CodePudding user response：

Since your excel version doesn't support TOCOL() and some other formulas you can use this simple solution:

=IF(LEN(A2)=10,A2,IF(LEN(B2)=10,B2,""))

Put it in C2 and drag id down for a result:

Since you didn't specify what to do if both columns has 10 digit number or both doesn't, in those cases it will return first 10 digit number or empty string.

CodePudding user response：

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.

What this formula does is get the first row of the first range where the string length is 10 and the string converted to a number does not produce an error (what would happen in case of text characters in the string).

When you drag down the formula it shows the second found, third, etc... until no values are found in the first range anymore.

In that case the IFERROR makes it look for the same logic in the second range.

As we want it to show the first found value first, we can't reset the ROW(1:1) * - that is used as a counter for the first smallest, second smallest, etc.. - * therefore we use the same counter and use SUMPRODUCT to subtract the total number of strings meeting the conditions in the first range. That way the counter will start at 1 for the second range and starts counting from there.

If no more values are found in the second range it will show a blank value. So you can drag down the formula up to the first blank result to show each result.

It's probably still slow with actual range references. I highly advise to upgrade to Office 365.