Repost:Converting Zip Codes

Hello, 
I have a program that export data to a csv file, then I 
save it as an Excel file.  The leading zeroes are 
truncated.  I have formatted them as zip codes in Excel 
and they look fine.  Here's the problem:  When I import 
them into Access (even though they look great in Excel), 
the leading zeroes are truncated.  

How do I go about getting the zip codes to import into 
Access correctly?

I have tried (concatenate("00",a1), but I don't always 
need zeroes, as in a zip code such as 34567.  Then I tried 
to determine the length and have it append the proper 
amount of zeroes, but the len function seems to always 
return true no matter what length I test for. 

I hope this makes sense. 

Thanks, D.

0
anonymous (74722)
10/17/2004 1:52:55 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
301 Views

Similar Articles

[PageSpeed] 41

Try this

=LEFT("00000",5-LEN(A17))&A17

-- 

HTH

RP

"D Collins" <anonymous@discussions.microsoft.com> wrote in message
news:15d201c4b450$9a99f1a0$a401280a@phx.gbl...
> Hello,
> I have a program that export data to a csv file, then I
> save it as an Excel file.  The leading zeroes are
> truncated.  I have formatted them as zip codes in Excel
> and they look fine.  Here's the problem:  When I import
> them into Access (even though they look great in Excel),
> the leading zeroes are truncated.
>
> How do I go about getting the zip codes to import into
> Access correctly?
>
> I have tried (concatenate("00",a1), but I don't always
> need zeroes, as in a zip code such as 34567.  Then I tried
> to determine the length and have it append the proper
> amount of zeroes, but the len function seems to always
> return true no matter what length I test for.
>
> I hope this makes sense.
>
> Thanks, D.
>


0
bob.phillips1 (6510)
10/17/2004 4:26:43 PM
A couple more:

=right(rept("0",5)&a1,5)
=text(a1,"00000")




D Collins wrote:
> 
> Hello,
> I have a program that export data to a csv file, then I
> save it as an Excel file.  The leading zeroes are
> truncated.  I have formatted them as zip codes in Excel
> and they look fine.  Here's the problem:  When I import
> them into Access (even though they look great in Excel),
> the leading zeroes are truncated.
> 
> How do I go about getting the zip codes to import into
> Access correctly?
> 
> I have tried (concatenate("00",a1), but I don't always
> need zeroes, as in a zip code such as 34567.  Then I tried
> to determine the length and have it append the proper
> amount of zeroes, but the len function seems to always
> return true no matter what length I test for.
> 
> I hope this makes sense.
> 
> Thanks, D.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/17/2004 4:37:10 PM
Hi

Although it is possible in Excel to format a cell as Zip Code in Excel, the
contents are still numeric.  When imported into Access, the leading zeros
will be removed.  Why not try formatting as text, this should then import as
text and retain the leading zeros.

"D Collins" <anonymous@discussions.microsoft.com> wrote in message
news:15d201c4b450$9a99f1a0$a401280a@phx.gbl...
> Hello,
> I have a program that export data to a csv file, then I
> save it as an Excel file.  The leading zeroes are
> truncated.  I have formatted them as zip codes in Excel
> and they look fine.  Here's the problem:  When I import
> them into Access (even though they look great in Excel),
> the leading zeroes are truncated.
>
> How do I go about getting the zip codes to import into
> Access correctly?
>
> I have tried (concatenate("00",a1), but I don't always
> need zeroes, as in a zip code such as 34567.  Then I tried
> to determine the length and have it append the proper
> amount of zeroes, but the len function seems to always
> return true no matter what length I test for.
>
> I hope this makes sense.
>
> Thanks, D.
>


0
bigwheel1 (14)
10/17/2004 9:24:55 PM
Hi

Although it is possible to format cells in Excel as Zip Code, the contents
remain as numeric.  This will be treated as a number when imported into
Access and will loose the leading zeros.  Try formatting as text which
should solve the problem

"D Collins" <anonymous@discussions.microsoft.com> wrote in message
news:15d201c4b450$9a99f1a0$a401280a@phx.gbl...
> Hello,
> I have a program that export data to a csv file, then I
> save it as an Excel file.  The leading zeroes are
> truncated.  I have formatted them as zip codes in Excel
> and they look fine.  Here's the problem:  When I import
> them into Access (even though they look great in Excel),
> the leading zeroes are truncated.
>
> How do I go about getting the zip codes to import into
> Access correctly?
>
> I have tried (concatenate("00",a1), but I don't always
> need zeroes, as in a zip code such as 34567.  Then I tried
> to determine the length and have it append the proper
> amount of zeroes, but the len function seems to always
> return true no matter what length I test for.
>
> I hope this makes sense.
>
> Thanks, D.
>


0
bigwheel1 (14)
10/17/2004 9:28:16 PM
Isn't that what he was trying to do when concatenating?

-- 

HTH

RP

"bigwheel" <bigwheel@nojunk.lineone.net> wrote in message
news:ckuo5i$4ud$1@newsg4.svr.pol.co.uk...
> Hi
>
> Although it is possible to format cells in Excel as Zip Code, the contents
> remain as numeric.  This will be treated as a number when imported into
> Access and will loose the leading zeros.  Try formatting as text which
> should solve the problem
>
> "D Collins" <anonymous@discussions.microsoft.com> wrote in message
> news:15d201c4b450$9a99f1a0$a401280a@phx.gbl...
> > Hello,
> > I have a program that export data to a csv file, then I
> > save it as an Excel file.  The leading zeroes are
> > truncated.  I have formatted them as zip codes in Excel
> > and they look fine.  Here's the problem:  When I import
> > them into Access (even though they look great in Excel),
> > the leading zeroes are truncated.
> >
> > How do I go about getting the zip codes to import into
> > Access correctly?
> >
> > I have tried (concatenate("00",a1), but I don't always
> > need zeroes, as in a zip code such as 34567.  Then I tried
> > to determine the length and have it append the proper
> > amount of zeroes, but the len function seems to always
> > return true no matter what length I test for.
> >
> > I hope this makes sense.
> >
> > Thanks, D.
> >
>
>


0
bob.phillips1 (6510)
10/17/2004 9:47:54 PM
Thanks for your input,

But, when the data is exported from the mainframe system, 
the leading zeroes are nonexistant.  Therefore, I need to 
add the zeroes back in.  That's where the problem lies.  
I could format it as text if I could get that far. 

One thing to consider as well, I have zip+4 that I have 
to add a zero to and also zip codes that might have all 
of their 5 digits, such as 12345-1234 and therefore don't 
want to add a zero. 

Thanks, D.
>-----Original Message-----
>Hi
>
>Although it is possible in Excel to format a cell as Zip 
Code in Excel, the
>contents are still numeric.  When imported into Access, 
the leading zeros
>will be removed.  Why not try formatting as text, this 
should then import as
>text and retain the leading zeros.
>
>"D Collins" <anonymous@discussions.microsoft.com> wrote 
in message
>news:15d201c4b450$9a99f1a0$a401280a@phx.gbl...
>> Hello,
>> I have a program that export data to a csv file, then I
>> save it as an Excel file.  The leading zeroes are
>> truncated.  I have formatted them as zip codes in Excel
>> and they look fine.  Here's the problem:  When I import
>> them into Access (even though they look great in 
Excel),
>> the leading zeroes are truncated.
>>
>> How do I go about getting the zip codes to import into
>> Access correctly?
>>
>> I have tried (concatenate("00",a1), but I don't always
>> need zeroes, as in a zip code such as 34567.  Then I 
tried
>> to determine the length and have it append the proper
>> amount of zeroes, but the len function seems to always
>> return true no matter what length I test for.
>>
>> I hope this makes sense.
>>
>> Thanks, D.
>>
>
>
>.
>
0
anonymous (74722)
10/27/2004 11:57:44 AM
Hello,

This didn't seem to work with zip+4, but will work with 
zip.

Thanks, D.
>-----Original Message-----
>Try this
>
>=LEFT("00000",5-LEN(A17))&A17
>
>-- 
>
>HTH
>
>RP
>
>"D Collins" <anonymous@discussions.microsoft.com> wrote 
in message
>news:15d201c4b450$9a99f1a0$a401280a@phx.gbl...
>> Hello,
>> I have a program that export data to a csv file, then I
>> save it as an Excel file.  The leading zeroes are
>> truncated.  I have formatted them as zip codes in Excel
>> and they look fine.  Here's the problem:  When I import
>> them into Access (even though they look great in 
Excel),
>> the leading zeroes are truncated.
>>
>> How do I go about getting the zip codes to import into
>> Access correctly?
>>
>> I have tried (concatenate("00",a1), but I don't always
>> need zeroes, as in a zip code such as 34567.  Then I 
tried
>> to determine the length and have it append the proper
>> amount of zeroes, but the len function seems to always
>> return true no matter what length I test for.
>>
>> I hope this makes sense.
>>
>> Thanks, D.
>>
>
>
>.
>
0
anonymous (74722)
10/27/2004 11:59:29 AM
Reply:

Similar Artilces:

scanning bar codes in pos
Is it possible to scan a bar code without having to press the enter key to make a transaction? It seems counter-productive to do it this way Frank, You should be able to program your scanner to add an 'ENTER' as a suffix to the scan. That is the way we do it. Marc "Frank" <nary@ec.rr.com> wrote in message news:1153175013.344333.97120@b28g2000cwb.googlegroups.com... > Is it possible to scan a bar code without having to press the enter key > to make a transaction? It seems counter-productive to do it this way > Some scanners require programming to s...

Must I convert my VB4 application with Jet DB if I install Office XP
I have a VB4(32) program that accesses an Access 97 database (.mdb) and seems to run fine under my recent upgrade to Windows 2000 Pro. I have Office 97 Pro installed but Windows 2000 Pro has major problems with Office 97 apps, so I am considering upgrading to Office XP. I have been unable to find any info about "fixing" Office 97 to operate properly under W2K Pro. (it often will not load and I have to end it with Task manager). I'm also faced with the problem of not being able to open Office Files I will receive from others using later versions of Office. -- Message posted vi...

Help correcting HTTP redirect code for OWA Exchange 2K3
Hi, I am using the code from Q article 55126 to redirect incomming http request to https for OWA. I keep getting the error below. Can someone help me with the solution to this. Thanks in advance --mdanny ****** Microsoft VBScript compilation error '800a0401' Expected end of statement /owa_redirect/owahttps.asp, line 1 If Request.ServerVariables("SERVER_PORT")=80 Then Dim strSecureURL strSecureURL = "https://" strSecureURL = strSecureURL & Request.ServerVariables("SERVER_NAME") strSecureURL = strSecureUR...

Getting Excel to return Hiragana using code/char functions
I have a Japanese computer which does this perfectly, but when I tried to use the worksheet on an English computer with Japanese language enabled, it only came up with errors. The code it was returning for the Japanese characters was much to low & putting in higher codes meant it didn`t recognise it. What can I do to make it work? ...

convert 1.2 miles to meters
can somebody tell me how many meters are in 1.2 miles? You can check out the Convert() function in the Help files. =CONVERT(1.2,"mi","m") Ans. = 1931.213 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "crlathem" <crlathem@discussions.microsoft.com> wrote in message news:B7EC7F92-19E9-4D70-A3A3-A5CAA918C6DB@microsoft.com... > can somebody tell me how many meters a...

Parse XML into Collection
I'm a newbie so please bear with me... I'm looking to parse XML into a Collection so that I can pass that collection around to functions to extract data from it easily. I'm hoping there is code already to parse std XML files into a Collection? All I've found is code where the XML structure is already known... I need to code to work no matter what the structure... it would "figure out" what nodes are parents/children and such then place them into a Collection appropriately. Any help is appreciated! An XmlDocument already defines a hierarchy of collections when it p...

Outlook converts my HTML email to text
But only occasionally, and I have to click on the message bar and choose Display as HTML to see it as it was meant to be seen. How can I stop Outlook from arbitrarily displaying HTML email as text? -- Dena Jo Email goes to denajo2 at the dot com variation of the Yahoo domain. Plonk the bastards: http://www.panix.com/~mwsm/trolls.html Why would you want to view mail in HTML? That is dangerous. All mail should be read and replied to in plain text for security reasons. "Dena Jo" <me@privacy.net> wrote in message news:Xns97D04AD6B6C9FDenaJo@130.133.1.4... > But only o...

Convert to PDF #2
This is the last time, but could someone convert two .pub to .pdf for me? Email me at squeakypants@gmail.com Why not just download pdf 995 and do it yourself for free? "squeakypants" wrote: > This is the last time, but could someone convert two .pub to .pdf for > me? Email me at squeakypants@gmail.com > > You can get a free pdf converter, Primopdf at www.primopdf.com or for a modest price get the better PDF-XChange at http://www.docu-track.com PDF-XChange has two advantages over the freebee programs, creates a smaller file (smaller than Adobe's) and makes e...

Activation Code for Money 2004 Deluxe
Can anyone get this link to work with either button. I keep getting the page can not be displayed. http://shop.microsoft.com/MoneyUpgrade/PurchaseQuestion.as p?SKU=860- 00211&SKUName=Mohttp://shop.microsoft.com/MoneyUpgrade/Pur chaseQuestion.asp?SKU=860-00211&SKUName=Money%20Deluxe% 202003%20English%20Upgrade%20From%20Money% 20Trial&img=/OMLibrary/images/X0880521frs.jpg Jason, Just use the link without the SKU: http://shop.microsoft.com/MoneyUpgrade/PurchaseQuestion.asp Good Luck! Regards, JB "jasona@arnell-west.com" <anonymous@discussions.microsoft.com>...

VBA codes question
Hi, I just write down some VBA codes for an application. What I need is extract some data from a selected area by mouse left-button. So what should I do ? The most I concerned is how to get the Range object of the shadow area selected by mouse. Thanks. ...

Convert File text box appears
I have the capability to to open resumes that have been submitted by potential future employees so that I can assign them to the correct department manager. Just recently I've started having a problem in that some of the resumes, after I launch them, I get a Convert File text window. I've tried every option in this list but I either get an error and nothing opens up or I get the boxes and other strange characters. I believe that the ones that I'm having problems with most likely were created in something other than Word, such as Works, Wordpad, Notepad, etc. I hav...

error code 0x80040154 my question is this, why doesn't MS take th
error code 0x80040154 my question is this, why doesn't MS take this error seriously? I came here for the first time because of this error. I was able to work around it ONLY because we had also purchased Office Pro. 2000 and can access the Clip Art from that disk. If anyone doesn't have this opportunity they are not going to be able to access their Clip Art in 2003 because of this error. I have come back and for two years individuals are still asking for help, a fix and a repair. NOTHING suggested ever helps or repairs this error and we have tried them all. I have the option of ...

convert text to formula using VLookup
My question; I've seen somewhere that you can convert a text string into a formula using the INDIRECT Function. What I am constructing is a maintenance Schedule where some items need to be serviced on a weekly basis, others monthly, quarterly, bi-annually, and yearly. (Those are the only five rotations I require at the moment.) Once constructed the format will take on the appearance of a calendar with conditional formatting to highlight the dates the service falls on. Each of the rotations require (I think) different formulas, so what I want to do is have a list of the f...

How void receipt/shipment with wrong Item account codes
Series : Purchase Order Processing http://img341.imageshack.us/img341/135/20100312185625.png No sure by create a Purchase Return can help =96 my concern is wrong account codes & the standard cost. Under Item maintenance, the item (V0910) has already with wrong account codes and wrong standard cost, Has been wrong even BEFOFE the creation of the receipt shipment. So, the Shipment transaction is now with the wrong accounts. I am wondering if by doing a purchase return (Transaction > Purchasing > Return Transaction Entry) can help to reversing all the wrong entries f...

Code completion not working in VC++ 6.0
Hi, Anyone seen this before? Code completion is not working properly in visual C++ 6.0. When I type . or ->, I no longer see the member list after it. Once I type in a function and ( then it will show me the parameters. But not the member list. I checked and I do have all the code completion options selected. What gives? Not sure when this stopped working, I haven't used it in quite some time (probably a couple years!) but now am using it again and this is driving me nuts. Am thinking maybe I should reinstall but would like to avoid that (especially since who knows if it will e...

Converting a date to a numeric (1-7 number) day of the week
I am trying to take a date (e.g., 01/01/2008) and have Access tell me the numeric day of the week (1-7) that corresponds to each date in a table. Thanks for your help! On Wed, 6 Feb 2008 08:00:03 -0800, John_ wrote: > I am trying to take a date (e.g., 01/01/2008) and have Access tell me the > numeric day of the week (1-7) that corresponds to each date in a table. > Thanks for your help! Look up the DatePart and the Weekday functions in VBA help. =DatePart("w", [DateField]) will return 4 if the day is a Wednesday. or... You can also use the Weekday function =WeekDay(...

Saving without VBA code
I have a workbook that has some VBA code in the ThisWorkbook module to define a toolbar button. This button uses VBA code in another module to save a set of ranges to a new spreadsheet file. I accomplish this by creating a new workbook, copying each range (each is on a separate worksheet), then saving the new workbook with a new name. Unfortunately, the VBA code in ThisWorkbook is also copied to the new workbook, so when the new spreadsheet file is opened, it also has the toolbar button. Is there any way to do this without also copying the VBA code from ThisWorkbook? Or, is there a way to remo...

Didn't convert FE to mde is that an issue?
Hi After splitting my database I didn't covert the FE to a mde. Is that an issue? If it is, how do I fix it? I also have to made some changes to the database. If I make changes to the backend, should I still link the FE that's not a mde or make the current FE to a MDE and then re-link it. Thank you in advance. On Fri, 23 Apr 2010 22:51:01 -0700, forest8 <forest8@discussions.microsoft.com> wrote: >Hi > >After splitting my database I didn't covert the FE to a mde. Is that an issue? > >If it is, how do I fix it? > >I also ha...

Please help with code! Thanks!
I have a workbook of about 26 worksheets. Since it=92s pretty big, I disabled the save & saveas so the user can only save the current sheet (the current sheet is copied to another workbook) and the user is given an option to saveas (saveas dialog) pops up. The Private Sub Workbook is in the Workbook while the Sub SavingFile is in the module. The code works well because most of the time, there is only one visible sheet, so the code copies the one sheet to another workbook for saving. The problem is that sometimes, the visible sheets are more than one sheet. In cases where there are more t...

help with coding
Hi All, I have been doing some search on this topic and require some coding help. I want to create a login form which checks for username, pwd and securitylevel and accordingly give access to certain queries , forms and reports. I do not want to use the Access user level security wizard and want to create a separate security module. I have come across this code posted by Klatuu which is probably the solution i am looking for. Public Function SetSecurityProp(UserInitials As String, SecurityLevel As Integer) As Boolean Dim prp As Property Const conPropNotFound As Integer = 3270 ...

how to converts a number to text eg. "2" become "two" #2
http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 many excel utilities from third parties have this built in http://xcell05.free.fr/ Morefunc for instance, see above link -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "CQ" <CQ@discussions.microsoft.com> wrote in message news:03EC3E12-2919-4F4F-9D6B-101F7C095860@microsoft.com... > ...

Converting dataset xml into adodb.recordset???
Hi, I have a need to conver the xml produced by a .net dataset (with a single table) into an adodb.recordset. The adodb.recordset doesn't understand the xml that the dataset.GetXml method produces. Can someone point me in the right direction? Thanks ... Ed Hi, see the code download for Dino Esposito's "Applied XML Programming for Microsoft .NET" http://www.microsoft.com/MSPress/books/6235.asp . In chapter 4 there is written as an example an XmlRecordsetWriter class (custom XML writer) which is capable to write ADO recordset compliant XML. It has WriteRecordset meth...

Help with code --> Invlaid use of Null????
Hi all: Can someone please let me know what I am doing wrong in the below code.....it is not all the code but just part of it. What I am doing is using a form to email copies of a report to our growers/customers. When the code loops through our grower/customer table and the grower has an email address it works perfect. However, when it hits a grower without an email address it then fails and gives me an "invalid use of null" error. I thought I was checking for "null" email addresses and the processing below what code I have shown will handle them. The pr...

X.400 Service error code 290
HI there, I require some assistance. We are running about a 20 user network and using Win NT SBS running Exchange 5.5 Thre is also a Linux Firewall setup which forwards incoming mail to the NT server. When sending mail the NT server forwards it to the Linux Box and then it gets sent. Everyone can send and receive both External and Internal email except one user who can do everything except send external email. The mail immediately bounces back with a error message from the administrator. When checking event viewer in the application log the following error has occured: A non-delive...

How to convert modeless dialog to modal
Hello, I have a modeless child dialog which I want to convert to modal ( I want it to stay on the top until it is closed and till then the parent window cannot be reached). Please lemme know how to do this. I tried setwindowpos with various options but it dint work Thanks, Ravi "ravi" <nelavelli82@gmail.com> wrote in message news:1142702556.206872.248450@u72g2000cwu.googlegroups.com... > Hello, > > I have a modeless child dialog which I want to convert to modal ( I > want it to stay on the top until it is closed and till then the parent > window cannot ...