Format phone numbers

[Alfajr]

Hi,

I have a list of numbers which are inputted by users in free form and I would like to have it in a consistent format removing "/", "(", "ext". Some cell lengths are greater than 10 and if the phone number begins with "1" I want to grab the next set of 10 numbers. I have uploaded a sample file, please help..

PS: I did already read the existing threads/posts but they couldn't tackle my issue.

 

[Faseeh]

Hi,

Do these numbers always start with 8XX series?? So that they can be used...

 

[Alfajr]

Hi,

Do these numbers always start with 8XX series?? So that they can be used...

No, they vary with area codes from all over the place. I tried to apply logic where it drops the 1st number if its <2 but that only works if the second number is not <2 also (havent figured out that part). Also, I have to keep multiplying the entire column by "1" to get it into number format from text.

 

[Faseeh]

What i was trying to do was to use this 8XX like code to work as an identifier of the desired number, can we have a list of such codes??

 

[Luke M]

Here's my attempt.

 

[Alfajr]

Here's my attempt.

Yayyy it worked, thank u so much, u have no idea how many diff tools I tried on this.. much appreciated.