Mass updating phone format?

[Kimi_Heskett1]

We have several versions of phone formatting in our Marketo DB... even for standard US 10 digit numbers. I know there are ways for force formatting on forms but has anyone done a mass cleanup of phone formatting? If so, share! 🙂

[Anonymous · Accepted solution]

I did a clean up last year where I exported the phone number column into excel. You can then strip the column of any existing formatting and use the "Special" option under Format Cells. 

Things to consider:
- If you have leads who do not have email addresses, make sure to export the SFDC Id with it so that you can utilize the SFDC data loader to update.
- We left international numbers as just a string of numbers because of the different formatting. The reps can format if they really need to. 
- Separate out phone numbers with extensions and deal with them on their own. This will help make the process go a little smoother.
- This can be time intensive.

I would love to hear some other ideas on this, though in case I need to do it again later on.

[Anonymous]

We did a similar clean up a year or so ago, but it required a lot of manual tweaking. 

We created a separate Phone Extension field and were able to use Excel's Text to Columns function to split a lot of the ones into a separate column using x as the delimiter,

We isolated phone numbers that were clearly North America, sorted them.  this revealed numbers that were missing digits or had too many and we had to delete some of these. 

Then we used Excel's Mid formula to extract the characters after "1-", "+1 ", etc.

As Jeff did , we stripped the numbers of any non-numeric characters and used the "Special" option in Format Cells to achieve a "(xxx) xxx-xxxx" format.  Salesforce also automatically formats 10 digit number to that format.

For international numbers to prevent Salesforce from autoformatting them, we use the format "+[country code] xxxxxxx"

[Kimi_Heskett1]

Thanks for the feedback Elliot and Jeff! We were thinking the same route as you guys but thought I'd see if someone had a miracle cure. You guys rock!

[Anonymous]

I have created a service for phone number formatting which can be used over webhooks.  This formats phone numbers and also provides geocoding (city/state info) for US and Canadian phone numbers.  Send me a note if interested in trying this out - rrajamani@marketo.com