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/
