SOLVED

Stripped 0 from beginning of postal code

Go to solution
Rachel_Noble
Level 10 - Champion Alumni

Stripped 0 from beginning of postal code

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.

Tags (2)
1 ACCEPTED SOLUTION

Accepted Solutions
Grégoire_Miche2
Level 10

Re: Stripped 0 from beginning of postal code

Hi Rachel,

Go for a webhook platform. Look at:

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

View solution in original post

8 REPLIES 8
SanfordWhiteman
Level 10 - Community Moderator

Re: Stripped 0 from beginning of postal code

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
Level 10 - Champion Alumni

Re: Stripped 0 from beginning of postal code

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
Level 10 - Champion Alumni

Re: Stripped 0 from beginning of postal code

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

Re: Stripped 0 from beginning of postal code

Hi Rachel,

Go for a webhook platform. Look at:

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
Level 10 - Champion Alumni

Re: Stripped 0 from beginning of postal code

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
Not applicable

Re: Stripped 0 from beginning of postal code

Hi Rachel, glad you found our free service useful!

SanfordWhiteman
Level 10 - Community Moderator

Re: Stripped 0 from beginning of postal code

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
Level 10 - Champion Alumni

Re: Stripped 0 from beginning of postal code

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