G
GD
How do you setup a column to accept an IP address format only, i.e.,
999.999.999.999?
999.999.999.999?
How do you setup a column to accept an IP address format only, i.e.,
999.999.999.999?
Hi,
2. If you want a stronger data validation then:
Use the custom with a formula of the following type
=AND(LEN(F1)=12,--LEFT(F1,3)<256,--MID(F1,4,3)<256,--MID(F1,7,3)<256,--MID(F1,10,3)<256)
Where you are applying it to the cell F1 to start. Now have the user enter
the IP address without decimal points and use the format I showed in #1.
Ron Rosenfeld said:Do you want IP address format only or do you want valid IP address format?
Valid IP addresses would have each octet in the range of 0-255.
So you would use Data/Validation Custom with the formula:
=AND(--LEFT(I1,FIND(".",I1)-1)<256,
--(MID(SUBSTITUTE(I1,".",REPT(" ",99)),99,99))<256,
--(MID(SUBSTITUTE(I1,".",REPT(" ",99)),198,99))<256,
--TRIM(RIGHT(SUBSTITUTE(I1,".",REPT(" ",99)),99))<256)
where I1 is the cell you are formatting.
Then execute copy/paste special/validation to copy the validation to the rest
of the column.
The above is not foolproof as it allows for "negative" octets. But a formula
using this approach won't work in the Data Validation box since there seems to
be a limit of 255 characters in the data validation formula box, and adding in
checks for the octets to also be >=0 would make this formula too long.
One way around that would be to put the full formula in a hidden "helper"
column, and use the data validation to check for TRUE or FALSE in that column.
--ron
This is exactly what i need. Thank you.