SOLVED

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

Go to solution
Jeremy_Caverly
Level 2

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

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

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. 

View solution in original post

6 REPLIES 6
SanfordWhiteman
Level 10 - Community Moderator

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

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
Level 2

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

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
Level 10 - Community Moderator

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

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
Level 2

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

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

jdubs_53
Level 1

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

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

SanfordWhiteman
Level 10 - Community Moderator

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

Unfortunately, Community rules prohibit me from pointing you directly.