+ Reply to Thread
Results 1 to 3 of 3

Validating IP ranges / text

  1. #1
    Registered User
    Join Date

    Validating IP ranges / text

    Hi All!

    I am still relatively new to working with the more advanced features of Excel, but I am getting a lot better - in part thanks to help I've found here = ) I've recently come across a pretty complex problem to solve, and I was hoping to get some input.

    I've been asked to validate entries in a column of a worksheet - only allowing valid IP address ranges to be entered OR text that specifically says "None." I don't really have control over the process that hands down these requests, but I am pretty much expected just to implement them as requested if at all possible.

    I know how to do basic data validation, but given some ranges of valid IP addresses and requiring Excel to check entries against that list OR against the word "None" is kind of beyond my abilities at this stage. Is this something that can even be done with basic validation, or do I need to try to code this in VBA somehow? OR - at the least, I assume it is possible to require a certain sequence to validate (like 1 to 3 characters, a dot, 1 to 3 characters, a dot, 1 to 3 characters, a dot, and 1 to 3 characters) - but I have had trouble allowing multiple dots ("."s) in one cell - apparently Excel does not like this ; )

    I've looked online and in the forum, but haven't found anything really helpful (other than one for-purchase programs that would do it automatically...).

    Any suggestions for the right direction would be very very helpful. Thanks so much for any hints / suggestions you may have!!
    Last edited by rokuk; 02-03-2008 at 10:45 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Hello rokuk,

    This should get you going. Here is UDF (User Defined Function) that you can use with your worksheets or with other VBA macro code.
    Please Login or Register  to view this content.
    Adding the Macro
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel

    UDF Worksheet Example
    Cell A1 contains the IP Address
    Cell A2 contains the IP Address
    Please Login or Register  to view this content.
    Leith Ross

  3. #3
    Registered User
    Join Date

    Thumbs up excellent answer!

    Thank you very much for your excellent and detailed answer! Since posting the question I've also come across the idea of using REGEX "regular expressions" as another possibility. I will try to figure out how your example works and go from there and perhaps look into REGEX a bit further.

    Thank you very much!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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