Forum Discussion
Extract country code from phone number
Hi.
I have a phone number column in a table in my model but the phone formats do not follow a specific format. I have phone numbers starting with +, others not; phone number separate with hyphens, others with spaces. It's a bit messy. I just wanted to know if there's a way to extract the country code from messy numbers.
Example of phone numbers:
| phone |
| +964 716X48X2 |
| 23080601X25X7 |
| +1 (609) 2X6 4X00 |
| 278 08X3 8X2 8X33 |
| +214 805X655X15 |
| +1 (40X) 6X7-12X1 |
Any help would be appreciated.
Hi Anonymous ,
Although the country code is not fixed, the number of digits in the phone number is fixed, which can be distinguished by the length of the phone number.
First use the replacement value function to remove unnecessary characters such as space, +, (,) and -.
Starting from the right, the tenth character begins to separate.
In case the digits of the telephone number are inconsistent, we also know that the international area code can be separated from the left and the third digit at most.
Hope to help you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Although the country code is not fixed, the number of digits in the phone number is fixed, which can be distinguished by the length of the phone number.
First use the replacement value function to remove unnecessary characters such as space, +, (,) and -.
Starting from the right, the tenth character begins to separate.
In case the digits of the telephone number are inconsistent, we also know that the international area code can be separated from the left and the third digit at most.
Hope to help you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am not fluent in phone number formats, but you may be able to use a combination of these functions to do this. For example, this expression gets rid of any characters that are not numbers or letters, and then takes the first 3 characters of the last 10 characters. You could also add a condition if the first character is 1 to use one expression or another if not.
= Text.Start(Text.End(Text.Select(Text.Upper([phone]), {"0".."9", "A".."Z"}), 10), 3)
Pat
