add leading zeros
 

In working on a database to remove redundancies and make it more efficent I
have run into a problme that I just do not know how to fix. Often when
restructuring tables it is easier to throw the data into EXCEL and then
import it back into the DB.

Becuase I must preserve the data that is currently in the DB rebuilding
tables can be madding. In this case I need to reformat the primary key which
is a field containing some SSNs, and some made up numbers of various lengths.

I want to reformat the feild to 9 charactors, but need to insert leading
zeros to the made up numbers. Example: one number is 118, and would need to
be reformated as 000000118.

There a several different number lengths which makes my job all the harder.
Is there a way to do this that I am not seeing? Any help will be greatly
appreciated...
--
Jacqueline

add leading zeros
 

Hi Jacqueline,

try on an auxilar column this formula

=rept("0",9-len(a2))&a2

hope this helps
regards from Brazil
Marcelo

"Jacqueline" escreveu:

In working on a database to remove redundancies and make it more efficent I
have run into a problme that I just do not know how to fix. Often when
restructuring tables it is easier to throw the data into EXCEL and then
import it back into the DB.

Becuase I must preserve the data that is currently in the DB rebuilding
tables can be madding. In this case I need to reformat the primary key which
is a field containing some SSNs, and some made up numbers of various lengths.

I want to reformat the feild to 9 charactors, but need to insert leading
zeros to the made up numbers. Example: one number is 118, and would need to
be reformated as 000000118.

There a several different number lengths which makes my job all the harder.
Is there a way to do this that I am not seeing? Any help will be greatly
appreciated...
--
Jacqueline

add leading zeros
 

Hi

For your example data:
=TEXT(118,"000000000")

Arvi Laanemets



"Jacqueline" wrote in message
...
In working on a database to remove redundancies and make it more efficent
I
have run into a problme that I just do not know how to fix. Often when
restructuring tables it is easier to throw the data into EXCEL and then
import it back into the DB.

Becuase I must preserve the data that is currently in the DB rebuilding
tables can be madding. In this case I need to reformat the primary key
which
is a field containing some SSNs, and some made up numbers of various
lengths.

I want to reformat the feild to 9 charactors, but need to insert leading
zeros to the made up numbers. Example: one number is 118, and would need
to
be reformated as 000000118.

There a several different number lengths which makes my job all the
harder.
Is there a way to do this that I am not seeing? Any help will be greatly
appreciated...
--
Jacqueline