Set Character limit based on another cell contents

[Mattmo]

Hi. I sure hope this is an easy one, but I can't seem to find the answer anywhere.

I have a template that lists addresses and need to make sure that when the country is United States, the Zip Code has 5 digits since state zips with leading zeros are always being dropped. Is there any way to do this using Data Validation formulas? I tried just an if statement but all that did was make ANY info entered in the zip code filed require 5 characters if the country was anything at all, so...that didn't work out. But I'm not incredibly sophisticated with excel formulas so maybe there's a better way?

 

[Joe4]

Welcome to the Board!

Have you tried pre-formatting the column holding Zip Code to be Text (instead of General or Number)?
If it is pre-formatted to be Text, it will not drop any leading zeroes which are entered.

 

[Mattmo]

Hi, thank you for replying. Yes, I've tried that, and it's helpful! But would like to have some sort of validation to ensure that all US zip codes are 5 digits, as the Zip Code is a requirement for certain tools to work properly.

 

[Joe4]

I think I would probably use VBA to automatically verify that.
What Column is the country code in, and what column is the Zip Code in?