+ Reply to Thread
Results 1 to 5 of 5

Subnetting Validation

  1. #1
    Registered User
    Join Date
    09-27-2015
    Location
    AZ
    MS-Off Ver
    2010
    Posts
    23

    Subnetting Validation

    Hello -

    I found a formula that will validate if the IP address that I enter in a cell is correct:

    Please Login or Register  to view this content.
    However, I this validation is only for the ip address (for example, 192.168.1.1).

    I need to validate the user is entering the correct IP with subnet information (for example, 192.168.1.1/24).

    Is there a way to modify this validation to look for the subnet cdr at the end of the IP? (/32, /31, /30..... /1).

    Thanks,

    Scott

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Subnetting Validation

    Hi,

    I'm not quite sure what your level of validation is here. For example, your current formula does nothing to disallow extra digits within the string: both

    192.168.1.1.999

    and

    192.0168.1.1

    to give just two examples, would both be admitted by your current validation formula.

    As to your question, an abbreviated version of your current formula would be.

    =SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(B1,".",REPT(" ",10)),{1,11,21,31},10),2)<=8))

    Is there a reason we can't just add a further condition to the above (or to your current formula) to check that the last two digits are >=0 and <=32?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    09-27-2015
    Location
    AZ
    MS-Off Ver
    2010
    Posts
    23

    Re: Subnetting Validation

    Thanks for your response!

    Looks like you are correct. My long validation does not really work properly.

    I like your simplified version. But not really sure I understand it. Plus it seems to output a value instead of true or false (192.168.1.1 = 4).

    I should have put more detail in my original post.

    My goal is to validate IP subnet so the address is somewhere between 0.0.0.0 and 255.255.255.255 and the cider at the end of the block is between /1 and /32.

    For example:
    192.168.1.1/24 = TRUE
    100.64.0.0/16 = TRUE
    1000.10.10.0/24 = FALSE
    10.10.10.0/45 = FALSE

    (I know the formula will not catch incorrect ip blocks... But this should catch little mistakes and makes sure the user enters the data in the field properly.)

    Seems like there would be an easy way to do this... Just not sure what it would be. Thanks again for looking at this for me! Really appreciate your help!

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Subnetting Validation

    Ok, if you're happy with a minimal level of validation as discussed:

    =SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(SUBSTITUTE(B1,"/","."),".",REPT(" ",10)),{1,11,21,31,41},10),2)<={8,8,8,8,5}))=5

    Regards

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Subnetting Validation

    We can try this also for A2

    =AND(ISNUMBER(FIND("/",A2)),SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(SUBSTITUTE(A2,"/","."),".",REPT(" ",10)),{1,11,21,31,41},10),2)<={8,8,8,8,5}))=5)
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] reversed validation: how to get an error message if the item IS on the validation list?
    By jokkebal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-29-2017, 09:29 AM
  2. update data validation sources if validation values change
    By mauler69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2016, 05:10 PM
  3. Replies: 3
    Last Post: 05-09-2016, 08:27 PM
  4. Data Validation Combo Box with Multiple Independent Validation Lists
    By firstofnine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2016, 04:38 PM
  5. Date and count validation in single cell - Custom Validation
    By murugavelmsc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2013, 05:25 AM
  6. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 AM
  7. data validation-Can the source of the validation criteria be dfrom a different sheet?
    By yael pinkert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2009, 04:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1