Conditioally insert characters to beginning of cell

[dcliffo]

I have a column of Supplier IDs. Maximum number of characters is 9. If the supplier ID is not 9 characters long, it should have 0's placed in the front so that there are 9 characters. For example, Supplier ID is 487695. It should look like 000487695. I can get Excel to format the cells correctly by using the custom format. However, when I import into Access, it trims the 0's away even though the field is identified as text because the custom format does not actually change the "value" of the cell. It merely formats the cells.

How can I get this column to correctly import into Access from Excel?

 

[mikerickson]

The above will give you trailing @'s, i.e. 12345 >> 12345@@@@

This will give you leading @'s, i.e. 12345 > > @@@@12345

=RIGHT("@@@@@@@@@"&A1,9)