Identify Indian Mobile Number from the List of Numbers in Column

[virbharat2000]

I am wondering for Identify Indian Mobile Number from the List of Numbers in Column.

Now, I have made a formula to identify the mobile number which closer to correct but not 100% correct if any body have better idea to have the formula more accurate without using any look up or Database.

=IF(OR(LEFT(IF(LEN(A47)=10,A47,IF(AND(OR(LEFT(A47,3)="091",LEFT(A47,3)="+91"),LEN(REPLACE(A47,1,3,""))=10),REPLACE(A47,1,3,""),IF(AND(LEFT(A47,2)="91",LEN(REPLACE(A47,1,2,""))=10),REPLACE(A47,1,2,""),IF(AND(LEFT(A47,1)="0",LEN(REPLACE(A47,1,1,""))=10),REPLACE(A47,1,1,""))))),1)="7",LEFT(IF(LEN(A47)=10,A47,IF(AND(OR(LEFT(A47,3)="091",LEFT(A47,3)="+91"),LEN(REPLACE(A47,1,3,""))=10),REPLACE(A47,1,3,""),IF(AND(LEFT(A47,2)="91",LEN(REPLACE(A47,1,2,""))=10),REPLACE(A47,1,2,""),IF(AND(LEFT(A47,1)="0",LEN(REPLACE(A47,1,1,""))=10),REPLACE(A47,1,1,""))))),1)="8",LEFT(IF(LEN(A47)=10,A47,IF(AND(OR(LEFT(A47,3)="091",LEFT(A47,3)="+91"),LEN(REPLACE(A47,1,3,""))=10),REPLACE(A47,1,3,""),IF(AND(LEFT(A47,2)="91",LEN(REPLACE(A47,1,2,""))=10),REPLACE(A47,1,2,""),IF(AND(LEFT(A47,1)="0",LEN(REPLACE(A47,1,1,""))=10),REPLACE(A47,1,1,""))))),1)="9"),IF(LEN(A47)=10,A47,IF(AND(OR(LEFT(A47,3)="091",LEFT(A47,3)="+91"),LEN(REPLACE(A47,1,3,""))=10),REPLACE(A47,1,3,""),IF(AND(LEFT(A47,2)="91",LEN(REPLACE(A47,1,2,""))=10),REPLACE(A47,1,2,""),IF(AND(LEFT(A47,1)="0",LEN(REPLACE(A47,1,1,""))=10),REPLACE(A47,1,1,""))))),"")

 

[Rick Rothstein]

I am wondering for Identify Indian Mobile Number from the List of Numbers in Column.

You are asking your question in an internation forum... not all of us know what an Indian Mobile Number is. It might be better if you show us some samples of what Indian Mobile Numbers can look like (try to cover whatever variability there may be in their construction, assuming there is more than one format for them). Also tell us any restrictions in the range for the numbers (or letters if applicable) as well.

 

[virbharat2000]

Thanks for your prompt reply.

1st Link is of my file
Mobile N Identify Indian Mobile Number from the List of Numbers in Columnumber Identifier - Upload

2nd Link of India Mobile Pattern
Mobile telephone numbering in India - Wikipedia, the free encyclopedia

3rd Link of India Landline STD Code
List of Indian STD codes-Karnataka- | BharatiyaMobile.com

The problem, I am facing is unable to differentiate between Mobile Number Start Digit & Land line STD Code Start Digit.

In my excel sheet, Cell A49 is Mobile Number or Land Line Number I am not able to differentiate.
Because in India there is no different in Mobile Length & Land Line Length, You can check the example of Banglore.

 

[virbharat2000]

Please reply asap.

 

[virbharat2000]

Rick, I am waiting for your reply. Or anyone having idea please help.

 

[AliGW]

Rick, I am waiting for your reply. Or anyone having idea please help.

I think the problem is that you are expecting helpers to do a lot of research in order to understand how Indian mobile numbers work before they can help you, which is somewhat unreasonable. You need to provide the necessary explanations either here or in your sample workbook, which should be provided via DropBox or similar.

 

[virbharat2000]

I think the problem is that you are expecting helpers to do a lot of research in order to understand how Indian mobile numbers work before they can help you, which is somewhat unreasonable. You need to provide the necessary explanations either here or in your sample workbook, which should be provided via DropBox or similar.

Yes, Aligw you are right. I thought that if we are able to resolve the issue with 95% accuracy that will also help the all community looking for this issue.

I will try to give you the whole situation & information of numbering pattern in India of Mobile & Land Line Std Codes.

In India mobile number could start from three numeric digits only 7,8 & 9. Our country code is +91.

So, I have written a formula which check length of the number if it is 10 then I check it is starting from 7,8 & 9 then assume as Mobile number else if it is greater than 10 then check & replace 0, 091, +91 as blank then again check length & if it 10 & start from 7,8,9 then assume as Mobile else no.

The problem start from here that in India Land Line Std codes also start from 7,8 & 9 in above formula those numbers are also coming which is a land line number not a mobile number.

Then I have also explore to match more digits up to 4 but the problem after matching up to 4 digits I can not bifurcate the number that it is a mobile or land line number. I require only mobile number.

"There is one solution, which I do not want to use. I can make a list of all std codes & match all the digits of that code to number comes after all the calculation & then decide that this number is mobile number or land line number. Why I do not want to use, 1) In India STD Codes update frequently (added extra digits) 2) I can't update every time the sheet as sheet will be used number of users seating in different location 3) Also mobile numbering pattern changes very dynamically"

Hope now you can understand the requirement.

You can download the sheet to understand better from scribbed link given on my second post of this problem.

Thanks
Vir

 

[AliGW]

I am sorry, but my AV software is telling me not to go to the site where your file is, so I am unable to download it. Sorry, but I just won't take the risk.

 

[virbharat2000]

I am sorry, but my AV software is telling me not to go to the site where your file is, so I am unable to download it. Sorry, but I just won't take the risk.

Thanks for update, please try below link, if you can.
https://shared.com/5s5xrr81yg?s=l

 

[virbharat2000]

Can anybody help me ?