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 in Others 26 views

## 1 answer to this question.

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.

• 53,160 points

## I want to revisit the recorded webinar session "Edureka Cloud Workshop - Build a Smarter ChatBot with AWS" held on Saturday, 10 October 2020 5:00 PM - 7:00 PM IST

Hi, @Subodh, If you have attended the workshop then ...READ MORE

## 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

## 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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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