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?

[Oaktree]

In a new column, use =TEXT(A1,rept("0",9)) and copy down. Use this new column for your import.

[dcliffo]

Thanks so very much! I knew it was simple, but I would have spent hours trying to figure that out. Never used rept before!

[Barry Katcher]

If you suffer from a mild case of Redundantbracketphobia, you can also try:

=TEXT(A1,"000000000")

[deepakgoyal2005]

What if i have a case like i need to extend my TEXT data to a particular length :

E.g. I have entries like -

AAA
BBBB
CCCCC

And i want -
AAA@@
BBBB@
CCCCC

Such that there are same number of characters in each cell in a column.
Please provide a solution ?

[PA HS Teacher]

How about:
=A1&REPT("@",MAX(5-LEN(A1),0))

[deepakgoyal2005]

Thanks Dude...

That solved my problemmmm

[brockk]

Originally Posted by PA HS Teacher

How about:
=A1&REPT("@",MAX(5-LEN(A1),0))

You probably want to refrase the formula cause when I tried it out, it works perfectly with the only exception that it completed it at the END of the cell and not the beginning as the op was requesting.

[mikerickson]

How about =LEFT(A1&"@@@@@@@@@",9)

[brockk]

Originally Posted by mikerickson

How about =LEFT(A1&"@@@@@@@@@",9)

Hi mikerickson.

Unfortunately that too completed towards the end and not the beginning. I guess when its a variable, its not as simple as it sounds to actually have it complete from the beginning.