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

0 votes

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

Jan 9, 2023 in Others by Kithuzzz
• 38,010 points
172 views

1 answer to this question.

0 votes

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.

enter image description here

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.

answered Jan 9, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,420 points
808 views
0 votes
1 answer

Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

To my knowledge, the only method to ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,420 points
489 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
1,541 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,232 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,418 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
696 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,325 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
528 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
952 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP