FYI, It works like a charm. :) Thanks again!
On 6/8/09 10:42 AM, "Corn Walker" <[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/ - Melissa Chappars Blackburn [email protected] Miami University Marketing Communications, 1130 UCM 102 Glos Center 513 529-7596 513 593-1664 cell
