beat me to it!
on 8/6/09 15:42, Corn Walker at [email protected] wrote: > On Jun 8, 2009, at 10:15 AM, Blackburn, Melissa Chappars Ms. wrote: > >> From the university Registrar¹s system, I downloaded several >> thousand student records into an Excel spreadsheet. The zip codes >> look fine in the Excel spreadsheet although the initial zero does >> not show in the Formula bar in Excel--the cells are formatted as zip >> code. >> >> From there I import them into an FMP 9 database where the zip codes >> lose their initial zeros in FileMaker. Is there a way I can correct >> the Excel sheet so I don¹t have to manually insert the missing zeros >> in FileMaker? > > Change the column type in Excel from "Zip Code" to "Text" although the > leading zero is long gone by now. As you stated, the initial zero > doesn't show in the Formula bar meaning it's not there for FMP to > import. The "zip code" format in Excel is simply a way of displaying > the data like a zip code, it doesn't put those leading zeros back in. > > You can deal with this in FMP fairly simply, though. You can place an > auto-enter calculation on your zipCode field such as the following: > > Case ( > not IsEmpty ( zipCode ) and Length ( zipCode ) < 5; Right ( "00000" > & ZipCode; 5 ); > zipCode > ) > > This auto-enter calculation allows you to "fix" the zip code entry on > the way in (when importing the records, check the box to perform auto- > enter options). > > Cheers, > -corn > > > Corn Walker > The Proof Group > http://proofgroup.com/ > > - - > Inbound email: This email has been scanned for viruses by MessageLabs > (Fourninety) Fourninety Ltd Tel: 0113 238 2447 ----------------------------------------------------------------------------------------------------------------------------------------- Fourninety Limited, a member company of the Media Square plc group. Registered in England No. 3737606. Registered address Clarence Mill, Clarence Road, Bollington, Cheshire. SK10 5JZ Confidentiality Notice: The information in this document and any attachments are confidential. It is intended only for the use of the named recipient. If you are not the intended recipient please notify us immediately and delete this document. The contents on this document must not be disclosed to any other person nor may any copies be taken. The views of the author may not represent the views of the Company. Security Warning: This e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. All liability for any claims arising as a result of using this medium to transmit information by us or to us is excluded to the extent permitted by law. -----------------------------------------------------------------------------------------------------------------------------------------
