International Phone number formating

[pvara]

I am curious if anyone has some macro that can identify and format a phone number into the International format. I am working on a CRM database with over 200 various countries and the phone number field is pretty clean in terms having the having the country code and number. However the ask is to "Text to Columns" for further analysis. For example:

USA Phone Number (11 Char)

17145551212

or

UK Phone Number (12 Char)

441234555555

Country Code	Area Code	Number
1	714	5551212
44	1234	555555

Any Suggestions would be great.

Thank you

[gc]

I have not, but this seems like a lookup table would do the trick. For each country name/code in a lookup table, maybe you have columns with the "parse length" of country code, area code and number. Then you reference those lengths in a Formula tool when you have to parse out the components of a specific telephone number by joining the data record with a lookup record on country name/code. Something like that. And you only build the lookup table once.

[SeanAdams]

Hey @pvara

Do you have the country handy in this data set?

Reason why I ask is that it's not obvious if the number is US (16643570193 meaning +1-664-357-0193) or if it's Monserrat (which has country code +(1-646) so the number would be (+1646)-357-0193)

There are several good sites out there for dialling codes / country codes, but due to this overlap in codes, and the fact that some countries still have variable length telephone numbers, there's no obvious way to know for certain just by looking at the phone number, but if you have the country code then you can look this up as @gc mentioned below, and then strip this part out of the phone number.   This would also allow you to do error checks on the data (e.g. this person is in USA, but their phone number doesn't begin with 1, is this an erroneous number, or an omission that just needs to be fixed by adding it on)

https://countrycode.org/

Let me know how this goes - if this helps to get you to a solution, would you mind marking as solved, or if you still have challenges, just reply on this thread.

Cheers @pvara

Sean

[pvara]

Hi Sean,

Thank you for the reply, within my CRM I do have the Country in my data set. I did make a "Lookup Table" out of the Country codes. Now I am left with the fun part the Formatting part. If anyone has any suggestion of tips or best practices that would be helpful.

Thanks

Pete

[SeanAdams]

:-) if you leave it to @MarqueeCrew, I'm sure he would throw in a regEx tool.

Me, I'm not that fancy, and so I'd do the following:

- Use your lookup code to find the country dialling code, and attach this to every row in your data-set using the country as a key.   Now you have 2 columns - teh long phone number, and the separate field with the country code from your lookup table

- trim off the first characters of your phone number that match to the length of your country code.  

     For example if your country code is +44, then ignoring the plus, you want to remove 44 from your phone number.   So 4480704321 becomes 80700431 (and you keep the country code in a separate column

- from there you can break up the phone number into sub-pieces arbitrarily.   US phone numbers are generally xxx-xxx-xxxx and India phone numbers are usually written xxxxx-xxxxx, so you can divide up a 10 digit number into either configuraiton and you'd be right

- So assume you do this 5 digits - you can split it in to 2 columns (left([phone number],5) and right([phone number],5)

- then just stitch it back together again.

Not sure if this makes sense - if not, let me know and I can mock this up with you (if you want to pass over a little realistic data in a table, we can mock up a flow that works with your specific data).

[pvara]

Thank you Sean- I think we are distant cousins or something because I am not that fancy either :), I am sure some fancy Regex will be needed at some point in the worksteam, but at least this approach will help me get started on this project. Thank you.

[MarqueeCrew · Accepted solution]

@pvara & @SeanAdams,

Are you taunting me to reply?  I'm not fancy, I'm lazy!

I've written non-RegEx code to standardize and perform hygiene on US & Mexican phone numbers & that was plenty of work.  If you've got hundreds of formats, my heart goes out to you.  Even my friends at:  http://regexlib.com/Search.aspx?k=phone+number doesn't have an expression to solve that many formats.

Within the US, you can find rules about what a valid prefix looks like.  Phone numbers like, 555-1212 or 711-1212 are not valid.  Doing this across the globe would be quite a task.  I somewhat like the idea of converting the phone number into RegEx expressions:

+1 714 516 2410  - Alteryx Headquarters

That could become:   +\d\w\d\d\d\w\d\d\d\w\d\d\d\d easily enough and then you could summarize on the formats found by country to see which formats are STANDARD and which need cleaning.

Just a thought.

Mark

[pvara]

@MarqueeCrew

Thank you for your suggestion and approach, I will review your posted link and start developing a workflow. Thank you again 

[SophiaF]

Looks like there is an API that does this for 232 countries - you do have to pay for over 5k API requests: https://numverify.com/

[pvara]

Hi @SophiaF great suggestion however due to strict company guidlines we are not allowed to send any data outside our network.