I need to import an Excel spreadsheet into Filemaker to convert it to a database. I have one column that displays UPC codes as 14 digit numbers in my spreadsheet. In Excel I can format the cells so that it always show 14 digits. For example if I enter 22211704423, Excel will add three leading zeros and format the cell as: 00022211704423

Is there any way I can do this in Filemaker?

Thanks

Hi coopersred

yes, you can do it in FileMaker.

Make a text field (UPCcodes) with AutoEnter/Replace option based on this calculation:

Right( "00000000000000" & UPCcodes ; 14 )

Whatever number you'll insert into the field, FM will convert it to a text field of 14 chars with the correct number of leading zeros.

I'm trying to duplicate the problem for my own knowledge.

Although the formula that Daniele provided will take care your need, I'm curious as to why FileMaker would be dropping these leading zeros, if they are actually there in the export. I did a test of export from Excel using a Text field with the zeros, and it imported (opened) with the leading zeros intact.

TIA for your feedback.

Lee

Edited August 1, 200619 yr by Guest

Thank you - that works perfect.

The orignal numbers in spreadsheet were imported to Filemaker without the cell formatting which was adding the leading zeros.

Filemaker imports data, not formatting. There never were any leading zeros in your actual Excel data.