Extract Phone Number

[Miss A!]

Hi there,

 

also caught up in a close to similar problem:

I would like to separate a contact number field into three separate fields, i have created the three separation fields as follows;

Country_Code, Area_Code and Direct_Dial

The contact number field has  data in different formats for various records such as;

+264812333249

+264 81 2444239

00264 81 2444239 etc,....

 

I tried to understand the solution on this topic for my situation but failed. is there even a possible solution for my situation?

Any help please - many thanx,

Miss A!

Edited April 16, 2015 by Lee Smith
changed subject to fit need

[comment]

This is not a "similar problem" - I don't know why you chose to append it here, instead of opening a new thread.

In any case, if it can be assumed that the 7 rightmost digits are the number, the next 2 digits (still counting from right to left) are the area code, and whatever remains to the left is the country code, then the problem is trivial. If not, then I don't see how it can be solved at all.

[Miss A!]

OMG - not easy i see as there is more variance on the formats, some records do not have country code or area code in the number field, just the direct dial such as;

081 2444239

81 2444 239

 

Thank you comment, i got a clue from your assumption but can't work it any way.

[comment]

some records do not have country code or area code in the number field, just the direct dial such as;

081 2444239

81 2444 239

​It doesn't matter, as long as they conform to the 3 rules I have stated.

[Lee Smith]

Missed A,

I have split your topic off into its own thread, because it was not the same need as the thread that you posted to.  In the  future, please just start your own thread, with  it's own subject instead of trying to use someone else’s.

 Take a look at this custom function, there are some others, here

[comment]

Take a look at this custom function, there are some others, here

​I don't think that's going to help here. The problem is extracting different portions of the number, not formatting it.

[pixi]

The problem is extracting different portions of the number, not formatting it.

​As long as I'm looking for a usable solution I haven't found anything which covers every possible aspect.

Currently, when the first number is one "0" I assume that it must be an area code. Checking the whole number again a list of area codes gives me the remaining number.

A "+" or two "00" are always country codes and I strip them off the same way. The remaining number get a "0" put in front and checked as described before.

[hbrendel]

I once made a function for this that is to be used in the Netherlands. The first part of it deals with country codes. Maybe you can tailor it to your needs.

The function is here.

[Lee Smith]

​I don't think that's going to help here. The problem is extracting different portions of the number, not formatting it.

Oops, I missed the extract.

 

Edited April 16, 2015 by Lee Smith

[Miss A!]

Is there a function i can use that goes by something like:

RightValues(text; start; numberofvalues)

basically something that states a staring point/position, the number of characters to return as well as from which side,

Thank you.

[comment]

​As long as I'm looking for a usable solution I haven't found anything which covers every possible aspect.

​It seems I am not able to make myself understood today. As long as the input follows the three rules stated in my first post, the solution is trivial:

 

1. the 7 rightmost digits are the number:

Number =

Let (
n = Filter ( Phonefield ; "0123456789" )
;
Right ( n ; 7 )
)

 

2. the next 2 digits (still counting from right to left) are the area code:

Area Code =

Let (
n = Filter ( Phonefield ; "0123456789" )
;
Left ( Right ( n ; 9 ) ; 2 )
)

 

3. whatever remains to the left is the country code:

Country Code =

Let (
n = Filter ( Phonefield ; "0123456789" )
;
GetAsNumber ( Left ( n ; Length ( n ) - 9 )  )
)

If the input does not conform to the three rules (or to some other rules that can be stated), then you need a psychic to solve this problem, not a computer.

[Miss A!]

Thank you so much - i am sure i can workout something from here.

[pixi]

​It seems I am not able to make myself understood today. As long as the input follows the three rules stated in my first post, the solution is trivial:

If the input does not conform to the three rules (or to some other rules that can be stated), then you need a psychic to solve this problem, not a computer.

​It might not be a matter of making yourself understood. It's just that some numbers don't follow your rules.

There is - a - structure over here (which no one really knows, btw). You can find:

Area code - phone number

030 - 123 45 67

0351 - 123 45

02174 - 12345-0

Fact is that phone numbers can have different lengths, from 4 to 9 numbers. The only real value you can check is the area code, therefore I suggested my way above. But that may only really work in Germany…

 

[comment]

​Fact is that phone numbers can have different lengths, from 4 to 9 numbers.

​Yes, that is correct. And country codes can be 1 - 3 digits long. Although the good news here is that once you know the first digit, you can also determine the length.

 

​when the first number is one "0" I assume that it must be an area code. Checking the whole number again a list of area codes gives me the remaining number.

​That is a good method, if:

1. area codes begin with a single "0" in all countries involved;

2. you have a list of area codes for each country involved.