+ Reply to Thread
Results 1 to 4 of 4

Format a cell as an IP address with no leading 0s and restricting "octets" to max of 255

  1. #1
    Registered User
    Join Date
    06-05-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Format a cell as an IP address with no leading 0s and restricting "octets" to max of 255

    I need to format a pretty simple table (8 columns, 6 rows) of cells to restrict the input to an IP address format. The output cannot have "leading 0s" -- so the number count might be different based on the particular IP address (for example, the addresses could be 123.12.1.1234 for one and 12.1234.1.12 for another). This will be a customer-facing input form -- so we're looking to make it as error-proof as possible by restricting the values to a format requirement/validation.

    1. Is there a way to format the cell to "force" the structure that would reflect four octets for an IP address and still be flexible on the number of digits per octet?
    2. Is there a way to prevent or alert if any of the octet "segments" are > 255 (the max on the IP range)?

    It is acceptable if I need to separate an individual IP address into individual columns for each octet, but was hoping to find a way to deliver each IP in a single cell... I'm just looking for a somewhat elegant solution to make the input as error-proof as possible. The initial form is attached, and I'm open to structural suggestions as long as the output appearsIP address question.xlsx similar to what we see here. Thanks in advance for your help.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Format a cell as an IP address with no leading 0s and restricting "octets" to max of 2

    Select all the IP address entry cells and go to Data|Validation, select Custom from Allow menu and enter formula:

    Please Login or Register  to view this content.
    you can enter an Error Message in the respective tab to alert user of either entering an octet with number larger than 255 or that they do not have exactly 4 quartets. If you want to make sure that they actually entered a number for each quartet and didn't leave it blank, then you can add 4 more conditions similar to the 0+TRIM(MID(SUBSTITUTE($C$4,".",REPT(" ",100)),1,100))<=255 conditions, checking each as 0+TRIM(MID(SUBSTITUTE($C$4,".",REPT(" ",100)),1,100))>0
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    09-21-2007
    Posts
    1

    Re: Format a cell as an IP address with no leading 0s and restricting "octets" to max of 2

    Hi,

    I've tried entering the above formula in Excel2010 but it's too long for the formula box. Am I missing something here?

    Cheers,

    Jon C

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Format a cell as an IP address with no leading 0s and restricting "octets" to max of 2

    I think you are right, it is longer than 255 characters. I think what I probably did was create the formula in a cell, checked out that it gave correct results... and automatically assumed it would work with Data Validation. The OP marked the thread Solved.. so all was "good".....

    Anyway, what you can do is place the formula in an adjacent cell, say D4. This should give a True/False result. Then the Data Validation in C4 would be simply =D4=TRUE

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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