Excel - zipcode

I have a column in Excel, I want to get only five digit. If value is four, I want to add zero in front, if more than five, get the first
five digit. So far, I have this. =LEFT(B2,5)
VBdotnet2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
Try:

=IF(LEN(B2)<=4,CONCATENATE("0",B2),IF(LEN(B2>=6),LEFT(B2,5),B2))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbaughanCommented:
try this, it takes into account any number of characters as input:
=CONCATENATE(LEFT("00000";5-LEN(B2));RIGHT(B2;5))
0
Ejgil HedegaardCommented:
You could also use

=TEXT(LEFT(B2,5),"00000")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sbaughanCommented:
Sorry, to get the "first" 5 digits, it should rather be
=CONCATENATE(LEFT("00000";5-LEN(B2));LEFT(B2;5))

@ Ejgil: Your solution looks even better than mine
0
Glenn RayExcel VBA DeveloperCommented:
Okay, with zip codes, you obviously need leading zeros.  And your data sounds like you may have Zip+4 codes without the hyphen.  What happens if you have a 4-digit zipcode (leading zero missing) with the +4 extra digits?  Your original algorithm would miss this and not add the missing leading zero first since there was more than five digits to begin with (i.e., eight total).

To account for this use this formula (inserted in row 2 and copied down)
=IF(LEN(B2)=9,LEFT(B2,5),IF(OR(LEN(B2)=8,LEN(B2)=4),"0"&LEFT(B2,4),B2&""))

Regards,
-Glenn
0
Steven HarrisPresidentCommented:
Nice catch Glenn.  I hadn't anticipated the +4 scenario.
0
VBdotnet2005Author Commented:
thank you
0
tomfarrarCommented:
How about this?

=IF(LEN(I6)>5,LEFT(I6,5), REPT("0", 5-LEN(I6))&I6)
0
Glenn RayExcel VBA DeveloperCommented:
@tomfarrar:
That returns the same results as Ejgil's formula.  It does not account for Zip+4 zip codes that should have a leading zero (i.e., only eight digits).

-Glenn
0
tomfarrarCommented:
Should have read responses more closely.  Thanks.
0
Glenn RayExcel VBA DeveloperCommented:
VBdotnet2005:

As shown here and in the attached example file, you'll see that not every formula returns the correct five digit master zip code from a set of zip codes that could range anywhere from 4 to 9 digits:
results from submitted formulas
It's not my intention to call anyone out; the point here is to show that the solutions you accepted produce different results than what I believe you actually need.   You qualified the data type in the title of your post, "Excel - zipcode", so I offered a solution that actually addresses this scenario.  If you actually have 8-digit data in your source column you will get an erroneous result with any of the accepted solutions.

I strive to give complete and descriptive solutions here at EE.  So as not to disappoint, I'll be more-careful when considering whether to provide solutions for you in the future.

 Regards,
 -Glenn
EE-ZipCodes.xlsx
1
VBdotnet2005Author Commented:
I don't need it anymore. Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.