Stripped 0 from beginning of postal code

[Rachel_Noble]

I apologize if this has already been answered - wasn't quite sure how to search for it. When we upload postal codes to Marketo via CSV, the file strips leading 0's from all postal codes. Our postal codes are set up as string fields in Marketo to allow international zip codes as well. Does anyone have a way to identify 4-digit postal codes and automatically append a zero out front? I'm thinking we could probably use a webhook service to do this, but I'm hoping for an easier way.

[SanfordWhiteman]

Postal Codes should always be Strings anyway (for exactly this reason, so zeroes aren't trimmed).  In the CSV, if the field is a string field there too, it won't do any trimming.

I can't think of a way to build a smartlist that will match any 4-character string. You could use a webhook, but for a one-time fixup that's a bit of overkill and I'd export and reimport.

[Josh_Hill13]

agree, just re-upload with Email Address | Zip Code and ensure the file is correct before you upload. Excel does funny things with zip code when not formatted correctly.

[Rachel_Noble]

Thanks guys. The real problem is that we have ongoing campaigns that send mail whenever a qualified lead is added to the system and has all the address info filled in. Leads are added in from multiple sources (many of which my team has no control over) so I'm just looking for an automatic way to fix these. Sounds like the answer might be outside of Marketo.

[Grégoire_Miche2 · Accepted solution]

Hi Rachel,

Go for a webhook platform. Look at:

• flowboost from Etumos. Contact Edward Unthank (Etumos)​
• CalcItNow. Contact Diederik Martens​
• Hoosh. Contact Fab Capodicasa
• BrightHook. Contact Rajesh Talele
• ...

Some of them will have a free level 1 service that might be just sufficient for this. But if you need some advanced logic (for instance to control the case where the leading 0 is missing vs when it is present, or if you need some country -based logic in a international environement), you might need to go for some advanced / chargeable logic.

-Greg

[Rachel_Noble]

Thanks Greg! I used Hoosh's Excel formula one to append a 0 for 4-character zip codes. It's not perfect but it's accurate enough for our use case. Thanks Fab Capodicasa​!

[Anonymous]

Hi Rachel, glad you found our free service useful!

[SanfordWhiteman]

Hmm... in FlowBoost: {{Lead.Postal Code}}.padLeft("0", 5)

But I'm uneasy (that's my word of the day) about the results when you don't know whether a zip is messed up on the left or the right -- that is, is "1376" supposed to be "01376" or "13760"?

To be sure, when you have a known-bad source (one that's using integers instead of strings) you can be confident. Just noting other cases where a simple fixup isn't really that simple and requires additional logic.

[Jessica_Kao3]

Hi Rachel,

When you format your list in excel, format the cell using custom with the following:

If a range of cells contains both five-digit and nine-digit postal codes (ZIP Codes), you can apply a custom format that displays both types of ZIP Codes correctly. In the Type box, type [<=99999]00000;00000-0000