virbharat2000
New Member
- Joined
- Apr 17, 2014
- Messages
- 14
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,""))))),"")
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,""))))),"")
Last edited: