LinkBack URL
About LinkBacks
I want to restrict valid entries on a cell to be only "DHCP" or an IP address of the form "xxx.xxx.xxx.xxx." What's the formula for this?
I want to restrict valid entries on a cell to be only "DHCP" or an IP address of the form "xxx.xxx.xxx.xxx." What's the formula for this?
This Data Validation formula might work. Checks for either DHCP or text in the format aaa.bbb.ccc.ddd (including the dots).
=OR(A1="DHCP",AND(LEN(A1)=15,ISNUMBER(MID(A1,1,3)+0),ISNUMBER(MID(A1,5,3)+0),ISNUMBER(MID(A1,9,3)+0),ISNUMBER(MID(A1,13,3)+0),MID(A1,4,1)=".",MID(A1,8,1)=".",MID(A1,12,1)="."))
It doesn't check if a valid IP is entered, of course (e.g. the individual numbers must be between 000 and 255), but that can be added.
Thanks for the help. How would I edit the formula to check for a valid IP?
What did you try?
I was going to edit the formula you posted, but it is too restrictive. For example, it won't allow 192.168.1.1. I think it will suffice to allow any numbers of the form aaa.bbb.ccc.ddd. No need for disallowing, for example, 777.777.777.777. Any idea?
It won't allow 192.168.1.1 because you stated that it must be in the form xxx.xxx.xxx.xxx. 192.168.1.1 is xxx.xxx.x.x. If you enter it as 192.168.001.001 it should work fine.
My apologies. I shouldn't have assumed blanks could be entered for an x (come to think of it, not entering anything isn't really a blank, although a space would probably work). I've never worked with data validation before, so I'm not sure how to check if it's a valid IP. Could you give me the concepts to use (e.g., some other functions to incorporate) and I'll play with them?
If you can use a UDF:
66.19.255.255 B2: =IsValidIP(A2) 65.0.0.0.0 65.16.256.255 66.80.0255.255 54.127.255.255 64.67.255.255 65.104.0.0 50.73.0.0 1.2.3.4
Select A2:A10, Data > Data Vailidation, Allow: Custom, Formula: =B2
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
The basic checks for valid IPv4's are:
1. Has four octets
2. Each octet is numeric
3. Each octet is between 0 and 255
If there isn't a strict data entry format, e.g. allowing 192.168.1.20 or 192.168.001.020, the test condition formulas get longer (not necessarily more difficult, though). Data Validation custom formula rules are limited to 255 characters - even in Excel 2013 which I'm using. To create a longer formula, which would likely be needed to test each of the conditions, you would probably want to use Named Ranges (1 or more) and then reference the results of those formulas. (Alternatively you could use VBA.)
The first rule - requiring four octets - can be partially determined by counting the number of dots in the value. There should be three. So this rule could check for that:
LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3
Unfortunately, this simply checks if there are three dots anywhere in the value. You could enter '192..4.1222' and it would result in TRUE even though it's obviously not a valid IP. This needs to be combined with other validation checks. Using formulas to do this would become quite cumbersome, but could be done. I'd recommend VBA for a more concise validation check, as shown in SHG's post above. (Thanks, by the way.)
I can definitely use a UDF and the one you posted works perfectly. However, I want to use it to disallow the entry if an invalid IP is entered. Do you know how I could implement it that way?
If you set up the data validation as described in the post, it will prevent manual entry of an invalid IP.
Post a workbook.
Sorry, workbook is attached.Originally Posted by shg
The data validation in A2:A3 works exactly as expected. What are you doing that slips by?
Last edited by shg; 07-30-2014 at 02:03 PM.
It returns the correct true/false value, but it doesn't prohibit invalid IP's from being entered (e.g., any IP that returns false is an invalid IP). How do I implement the UDF to not allow invalid IP's at all? Usually, when the input doesn't meet the validation criteria, it gives an error informing the user and deletes the input.
I'm just not seeing it. Have you enabled macros?
Paul, does it work for you?
The file uploaded in post 14 is working properly for me as well, shg. If I type in an IP with more or less than three dots, or if one of the values is greater than 255, I'm prevented from doing so by Data Validation.
Thanks, Paul, you see what I see.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks