Jump to content

Extract Phone Number


This topic is 2786 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Lee Smith
changed subject to fit need
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

​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 by Lee Smith
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

​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.

  • Like 1
Link to comment
Share on other sites

​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…

 

Link to comment
Share on other sites

​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.

 

 

 

 

Link to comment
Share on other sites

This topic is 2786 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.