gokuleshdasa
New Member
God morning all,
I am a Manager in an NGO and deal with hell lots of excel files. I am struggling with this problem from past 6 years and did not get any proper solution but manual "Text to Columns splitter" & Find-Replace functions etc.
I humbly request experts here to help me. It will be useful for many people like me.
Okay so going straight, I get data always from different people and it is in this format:
91 80 40931584(R) 9980099607(M) -
999999999999999(M) - this is not valid. **Remove**
91 - 9886033006 91-080-9886033006
0877 3120132 91-0877-2241056
0986002107291--
+919380519217 +91-44-2447 5200
+919380519217
+91-44-2447 5200
In the case of landline nos. 91 is country code and 80 is city code(which can be 2-4 digits) and after that 7 digit(40931584) is telephone no. If any occurance VBA finds then it should produce result like: 08040931584 removing all 91s.
In the case of mobile nos. Mobiles will start from 7/8/9 eg 9980099607 and they will be strictly 10 digits. AI should remove all +91s and sometimes 91(no plus). This creates so much confusion.
I want some VBA Script which works with Artificial Intelligence and understands all mobile numbers and landline numbers and clears them and distributes them in new columns if need be. If its only 1 number leave it there only.
Final Result I am expecting should be:
08040931584 9980099607
9886033006 9886033006 (91-080-removed because it is a mobile and doesnt require city and country code)
08773120132 08772241056
986002107291
9380519217 04424475200
9380519217
+91-44-2447 5200
You will really save my life. I will pray for you!!!
I am a Manager in an NGO and deal with hell lots of excel files. I am struggling with this problem from past 6 years and did not get any proper solution but manual "Text to Columns splitter" & Find-Replace functions etc.
I humbly request experts here to help me. It will be useful for many people like me.
Okay so going straight, I get data always from different people and it is in this format:
91 80 40931584(R) 9980099607(M) -
999999999999999(M) - this is not valid. **Remove**
91 - 9886033006 91-080-9886033006
0877 3120132 91-0877-2241056
0986002107291--
+919380519217 +91-44-2447 5200
+919380519217
+91-44-2447 5200
In the case of landline nos. 91 is country code and 80 is city code(which can be 2-4 digits) and after that 7 digit(40931584) is telephone no. If any occurance VBA finds then it should produce result like: 08040931584 removing all 91s.
In the case of mobile nos. Mobiles will start from 7/8/9 eg 9980099607 and they will be strictly 10 digits. AI should remove all +91s and sometimes 91(no plus). This creates so much confusion.
I want some VBA Script which works with Artificial Intelligence and understands all mobile numbers and landline numbers and clears them and distributes them in new columns if need be. If its only 1 number leave it there only.
Final Result I am expecting should be:
08040931584 9980099607
9886033006 9886033006 (91-080-removed because it is a mobile and doesnt require city and country code)
08773120132 08772241056
986002107291
9380519217 04424475200
9380519217
+91-44-2447 5200
You will really save my life. I will pray for you!!!
