Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
#5
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating formulas that allow the solutions to start with zeros. | Excel Discussion (Misc queries) | |||
How do I force leading zeros in an Excel cell? | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
Excel file saved as csv - dropping zeros | Excel Discussion (Misc queries) | |||
Displaying leading zeros in an Excel spreadsheet | Excel Discussion (Misc queries) |