Data Cleansing - Remove Dashes '-' from Mobile Number Field

[Jeremy_Caverly]

I want to standardize my Mobile Number field to just the 10-digit of the phone number.

Is there a way to use two different Smart Campaigns?

1. IF Mobile Number Contains '-'  >> Write "wrong" data value to a second "To-Be-Cleaned Phone" Custom Field for cleaning
2. WHEN "To-Be-Cleaned Phone" Custom Field changes to something >> Write that data, minus the dashes '-' back to the Mobile Number field

Marketo Support suggested I do this by exporting a list, making the corrections in Excel and then re-importing with email address as the de-dupe field. I need this to be automated to ensure a Webhook call can run properly that depends on this Mobile Number field as a token and the POST endpoint only accepts the 10-digit number.

Any ideas of a potential solution that I'm not even aware of?

[SanfordWhiteman]

Is there a way to use two different Smart Campaigns?

1. IF Mobile Number Contains '-'  >> Write "wrong" data value to a second "To-Be-Cleaned Phone" Custom Field for cleaning
2. WHEN "To-Be-Cleaned Phone" Custom Field changes to something >> Write that data, minus the dashes '-' back to the Mobile Number field

Yes, but you clearly need an initial webhook to cleanse the data.

You can call the Twilio Lookup API for that, or use a more robust service if you need other fixup.

[Jeremy_Caverly]

Yeah, was actually trying to do the same thing with couple Smart Campaigns. Definitely can try to leverage the Twilio Lookup since we're already using Triggered Webhook's to Twilio for sending SMS messages.

However, it looks like Twilio's service is responding with US-Formatted structure of (555) 555-5555 ... that would still leave me with characters I don't want in my field. Am I just not seeing an option in the API call to them to return 10-digits ONLY? Or do you know of something else that might help me strip all but the numerical digits?

[SanfordWhiteman · Accepted solution]

The response also contains the E.164 version, which is unpunctuated except for the leading +.

If you must have the plus sign stripped then... well, check some of my posts from a couple of years back. 

[Jeremy_Caverly]

Thanks, Sanford!
 
Would that be posts here? Or on your blog? Am I looking for "strip", "punctuation", "RegEx"?

[jdubs_53]

Did you ever find the links he referenced? I've searched and not found what I believe he referenced. 

[SanfordWhiteman]

Unfortunately, Community rules prohibit me from pointing you directly.