add zeros
 

Is there a function that will add zeros to the beginning of my data. I have
four fields that consist of the four octets for an IP address. I would like
to create a function that will pad each field with zeros if the field is less
than 3 numbers. Such as if the existing data is 1, I want it to change the
data to 001 or if the the data is 10, I want it to be 010. Also, if the data
is 0, I want it to be 000. I actually want the data to change, not just the
way it displays. I intend on importing this changed data into an Access
database. If there is a better way to do this, I am open to suggestions.
Thanks. Matt

No function needed, just give the cells the custom format: 000
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Matt" wrote in message
...
Is there a function that will add zeros to the beginning of my data. I
have
four fields that consist of the four octets for an IP address. I would
like
to create a function that will pad each field with zeros if the field is
less
than 3 numbers. Such as if the existing data is 1, I want it to change
the
data to 001 or if the the data is 10, I want it to be 010. Also, if the
data
is 0, I want it to be 000. I actually want the data to change, not just
the
way it displays. I intend on importing this changed data into an Access
database. If there is a better way to do this, I am open to suggestions.
Thanks. Matt

Thanks Bernard. That worked great. Now is there a way to combine these four
fields into one separated by periods so that it appears as an IP address.
example
010.100.010.000. Thanks. Matt

"Bernard Liengme" wrote:

No function needed, just give the cells the custom format: 000
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Matt" wrote in message
...
Is there a function that will add zeros to the beginning of my data. I
have
four fields that consist of the four octets for an IP address. I would
like
to create a function that will pad each field with zeros if the field is
less
than 3 numbers. Such as if the existing data is 1, I want it to change
the
data to 001 or if the the data is 10, I want it to be 010. Also, if the
data
is 0, I want it to be 000. I actually want the data to change, not just
the
way it displays. I intend on importing this changed data into an Access
database. If there is a better way to do this, I am open to suggestions.
Thanks. Matt

=TEXT(A1,"000")&"."&TEXT(B1,"000")&"."&TEXT(C1,"00 0")&"."&TEXT(D1,"000")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matt" wrote in message
...
Is there a function that will add zeros to the beginning of my data. I
have
four fields that consist of the four octets for an IP address. I would
like
to create a function that will pad each field with zeros if the field is
less
than 3 numbers. Such as if the existing data is 1, I want it to change
the
data to 001 or if the the data is 10, I want it to be 010. Also, if the
data
is 0, I want it to be 000. I actually want the data to change, not just
the
way it displays. I intend on importing this changed data into an Access
database. If there is a better way to do this, I am open to suggestions.
Thanks. Matt

=text(a1,"000.")&text(b1,"000.")&text(c1,"000.")&t ext(d1,"000")

Matt wrote:

Thanks Bernard. That worked great. Now is there a way to combine these four
fields into one separated by periods so that it appears as an IP address.
example
010.100.010.000. Thanks. Matt

"Bernard Liengme" wrote:

No function needed, just give the cells the custom format: 000
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Matt" wrote in message
...
Is there a function that will add zeros to the beginning of my data. I
have
four fields that consist of the four octets for an IP address. I would
like
to create a function that will pad each field with zeros if the field is
less
than 3 numbers. Such as if the existing data is 1, I want it to change
the
data to 001 or if the the data is 10, I want it to be 010. Also, if the
data
is 0, I want it to be 000. I actually want the data to change, not just
the
way it displays. I intend on importing this changed data into an Access
database. If there is a better way to do this, I am open to suggestions.
Thanks. Matt




--

Dave Peterson