Erase extensions from Phone field

[Sam_Sweeney]

Through a quirk in our system, we are not permitted to have extensions in the phone field. They must be broken out into a separate "Ext" field. This is an issue for many of our vendors who are unable to append their data to have this broken out into 2 different fields. We have been able to map the extension to the EXt field but now are looking for a way to auto erase the extension from the original phone field.

Ex:

Incoming data: 555-444-0123 x52

Mapped data: Ext: 52 phone: 555-444-0123 x52

Desired Result: Ext: 52 phone: 555-444-0123

Please let me know if you have a good way to have this field auto remove the extension from the phone field.

[Josh_Hill13]

Data tools like Openprise could do this.

Alternatively, you can export this to Excel, run the split, then re-upload. This assumes no dupes.

[Bonnie_Jeffers2]

Hi Josh,

I've noticed you mention Openprise on a couple of data management threads. Are you using this tool? Do you personally recommend it?

[Josh_Hill13]

There are tools like Openprise, RingLead, LeanData that allow diff kinds of complex data processing.

Based on demos, I do like Openprise. Depends on what you need to do.

If you are able to do webhooks, that's a good low cost option. I believe someone setup a low cost webhook tool for marketo somewhere. Or you can use zapier instead.

[SanfordWhiteman]

A webhook can do this fixup very easily. If it always uses the format in your example, it's a simple regex replacement. For example, in Java (though this could be written in any modern language):

phoneNumber = phoneNumber.replaceFirst("\sx\d+$","");

You can also use the free Twilio lookup API for this (see Calling the Twilio Lookup API as a Marketo webhook). The phone_number property in the response is always the phone number without the extension. The only difference is that it uses (as is technically proper) the E.164 version of the number, with a + prepended.