On Sun, 19 Oct 2008 13:57:01 -0700, GD wrote:

How do you setup a column to accept an IP address format only, i.e.,
999.999.999.999?

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