[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - zipcode

Posted on 2014-08-18
13
Medium Priority
?
214 Views
Last Modified: 2016-04-06
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)
0
Comment
Question by:VBdotnet2005
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 668 total points
ID: 40268726
Try:

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

Assisted Solution

by:sbaughan
sbaughan earned 668 total points
ID: 40268792
try this, it takes into account any number of characters as input:
=CONCATENATE(LEFT("00000";5-LEN(B2));RIGHT(B2;5))
0
 
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 40268793
You could also use

=TEXT(LEFT(B2,5),"00000")
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 5

Expert Comment

by:sbaughan
ID: 40268804
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40268808
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
 
LVL 18

Expert Comment

by:Steven Harris
ID: 40268887
Nice catch Glenn.  I hadn't anticipated the +4 scenario.
0
 

Author Comment

by:VBdotnet2005
ID: 40268941
thank you
0
 
LVL 7

Assisted Solution

by:tomfarrar
tomfarrar earned 664 total points
ID: 40288585
How about this?

=IF(LEN(I6)>5,LEFT(I6,5), REPT("0", 5-LEN(I6))&I6)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40288600
@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
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40288603
Should have read responses more closely.  Thanks.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40289270
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
 

Author Comment

by:VBdotnet2005
ID: 41539341
I don't need it anymore. Thank you
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question