• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Seperate Landline and Mobile numbers from cluttered text through VBA


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
+919380519217 +91-44-2447 5200
+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
9380519217 04424475200
+91-44-2447 5200

You will really save my life. I will pray for you!!!

Marc L

Excel Ninja
Wild cross posting ‼​
As a reminder :​