Mass updating phone format? | Community
Skip to main content
Kimi_Heskett1
Kimi_Heskett1
Level 9
May 29, 2013
Solved

Mass updating phone format?

  • May 29, 2013
  • 4 replies
  • 2167 views
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! :)
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by
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.

4 replies

A
Anonymous
Accepted solution
May 29, 2013
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.
    A
    Anonymous
    May 30, 2013
    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
      Kimi_Heskett1Author
      Level 9
      May 30, 2013
      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!
      A
      Anonymous
      August 9, 2013
      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
      Terms & ConditionsAccessibility statement

      Loading footer...