ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   IP Address Validation? (https://www.excelbanter.com/excel-discussion-misc-queries/206964-ip-address-validation.html)

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

ExcelBanter AI
Answer: IP Address Validation?
 
To set up a column in Microsoft Excel to accept an IP address format only, you can use Data Validation. Here are the steps:
  1. Select the column where you want to apply the validation.
  2. Go to the Data tab in the ribbon and click on Data Validation.
  3. In the Data Validation dialog box, select "Custom" from the "Allow" drop-down menu.
  4. In the "Formula" field, enter the following formula:
    Formula:
    =AND(ISNUMBER(--SUBSTITUTE(A1,".","")),(LEN(A1)-LEN(SUBSTITUTE(A1,".","")))=3) 
  5. Replace "A1" with the first cell of the selected column.
  6. Click on the Error Alert tab and enter a message that will appear if the user enters an invalid IP address format, such as "Please enter a valid IP address format (e.g. 192.168.0.1)."
  7. Click OK to close the dialog box.

Now, when a user tries to enter a value in the selected column that does not match the IP address format (e.g. 123.456.789), they will see an error message and will not be able to proceed until they enter a valid IP address format.

Ron Rosenfeld
IP Address Validation?
 
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

Barb Reinhardt
IP Address Validation?
 
You could put something in a worksheet change instead of data validation. Of
course, that would assume that macros are enabled.

Barb Reinhardt

"Ron Rosenfeld" wrote:

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

ShaneDevenshire
IP Address Validation?
 
Hi,

1. An alternate but not quite as complex a solution would be to choose
Data, Validation, Whole Numbers, Between 100000000000 and 999999999999.
And then to apply a custom format to the cell of 000"."000"."000"."000 by
going to Format, Cell, Number tab, Custom. Or if a valid address could be
000.000.000.001 then set the lower limit to 1.

2. If you want a stronger data validation then:
Use the custom with a formula of the following type
=AND(LEN(F1)=12,--LEFT(F1,3)<256,--MID(F1,4,3)<256,--MID(F1,7,3)<256,--MID(F1,10,3)<256)
Where you are applying it to the cell F1 to start. Now have the user enter
the IP address without decimal points and use the format I showed in #1.


If this is helpful, click the Yes button.
--
Thanks,
Shane Devenshire


"GD" wrote:

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

Ron Rosenfeld
IP Address Validation?
 
On Sun, 19 Oct 2008 16:25:00 -0700, ShaneDevenshire
wrote:

Hi,


2. If you want a stronger data validation then:
Use the custom with a formula of the following type
=AND(LEN(F1)=12,--LEFT(F1,3)<256,--MID(F1,4,3)<256,--MID(F1,7,3)<256,--MID(F1,10,3)<256)
Where you are applying it to the cell F1 to start. Now have the user enter
the IP address without decimal points and use the format I showed in #1.

This disallows many acceptable IP addresses. Among others, it will exclude any
IP addresses where the first octet is less than 100. It also requires that
each octet be entered as three digits, which is not required for a valid IP
address.
--ron

GD
IP Address Validation?
 
This is exactly what i need. Thank you.

"Ron Rosenfeld" wrote:

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

Ron Rosenfeld
IP Address Validation?
 
On Sun, 19 Oct 2008 20:21:00 -0700, GD wrote:

This is exactly what i need. Thank you.

You're welcome. Glad to help. Thanks for the feedback.
--ron

Craig Hatmaker
IP Address Validation?
 
Your basic idea revised. Under 256 characters. Checks for negatives as well.

=IFERROR(AND(
VALUE(MID(SUBSTITUTE(A54,".",REPT(" ",6)),{1,8,15,22},{3,5,7,9}))<256,
VALUE(MID(SUBSTITUTE(A54,".",REPT(" ",6)),{1,8,15,22},{3,5,7,9}))=0),FALSE)

On Sunday, October 19, 2008 4:57 PM G wrote:

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


On Sunday, October 19, 2008 6:23 PM Ron Rosenfeld wrote:

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


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


On Sunday, October 19, 2008 7:10 PM BarbReinhard wrote:

You could put something in a worksheet change instead of data validation. Of
course, that would assume that macros are enabled.

Barb Reinhardt

"Ron Rosenfeld" wrote:


On Sunday, October 19, 2008 7:25 PM ShaneDevenshir wrote:

Hi,

1. An alternate but not quite as complex a solution would be to choose
Data, Validation, Whole Numbers, Between 100000000000 and 999999999999.
And then to apply a custom format to the cell of 000"."000"."000"."000 by
going to Format, Cell, Number tab, Custom. Or if a valid address could be
000.000.000.001 then set the lower limit to 1.

2. If you want a stronger data validation then:
Use the custom with a formula of the following type
=AND(LEN(F1)=12,--LEFT(F1,3)<256,--MID(F1,4,3)<256,--MID(F1,7,3)<256,--MID(F1,10,3)<256)
Where you are applying it to the cell F1 to start. Now have the user enter
the IP address without decimal points and use the format I showed in #1.


If this is helpful, click the Yes button.
--
Thanks,
Shane Devenshire


"GD" wrote:


On Sunday, October 19, 2008 9:48 PM Ron Rosenfeld wrote:

On Sun, 19 Oct 2008 16:25:00 -0700, ShaneDevenshire
wrote:


This disallows many acceptable IP addresses. Among others, it will exclude any
IP addresses where the first octet is less than 100. It also requires that
each octet be entered as three digits, which is not required for a valid IP
address.
--ron


On Sunday, October 19, 2008 11:21 PM G wrote:

This is exactly what i need. Thank you.

"Ron Rosenfeld" wrote:


On Monday, October 20, 2008 7:52 AM Ron Rosenfeld wrote:

You're welcome. Glad to help. Thanks for the feedback.
--ron

Craig Hatmaker
IP Address Validation?
 
Your basic idea revised. Under 256 characters. Checks for negative values.

=IFERROR(AND(
VALUE(MID(SUBSTITUTE(A54,".",REPT(" ",6)),{1,8,15,22},{3,5,7,9}))<256,
VALUE(MID(SUBSTITUTE(A54,".",REPT(" ",6)),{1,8,15,22},{3,5,7,9}))=0),FALSE)

On Sunday, October 19, 2008 4:57 PM G wrote:

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


On Sunday, October 19, 2008 6:23 PM Ron Rosenfeld wrote:

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


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


On Sunday, October 19, 2008 7:10 PM BarbReinhard wrote:

You could put something in a worksheet change instead of data validation. Of
course, that would assume that macros are enabled.

Barb Reinhardt

"Ron Rosenfeld" wrote:


On Sunday, October 19, 2008 7:25 PM ShaneDevenshir wrote:

Hi,

1. An alternate but not quite as complex a solution would be to choose
Data, Validation, Whole Numbers, Between 100000000000 and 999999999999.
And then to apply a custom format to the cell of 000"."000"."000"."000 by
going to Format, Cell, Number tab, Custom. Or if a valid address could be
000.000.000.001 then set the lower limit to 1.

2. If you want a stronger data validation then:
Use the custom with a formula of the following type
=AND(LEN(F1)=12,--LEFT(F1,3)<256,--MID(F1,4,3)<256,--MID(F1,7,3)<256,--MID(F1,10,3)<256)
Where you are applying it to the cell F1 to start. Now have the user enter
the IP address without decimal points and use the format I showed in #1.


If this is helpful, click the Yes button.
--
Thanks,
Shane Devenshire


"GD" wrote:


On Sunday, October 19, 2008 9:48 PM Ron Rosenfeld wrote:

On Sun, 19 Oct 2008 16:25:00 -0700, ShaneDevenshire
wrote:


This disallows many acceptable IP addresses. Among others, it will exclude any
IP addresses where the first octet is less than 100. It also requires that
each octet be entered as three digits, which is not required for a valid IP
address.
--ron


On Sunday, October 19, 2008 11:21 PM G wrote:

This is exactly what i need. Thank you.

"Ron Rosenfeld" wrote:


On Monday, October 20, 2008 7:52 AM Ron Rosenfeld wrote:

You're welcome. Glad to help. Thanks for the feedback.
--ron


On Saturday, August 27, 2011 8:39 PM Craig Hatmaker wrote:

Your basic idea revised. Under 256 characters. Checks for negatives as well.



=IFERROR(AND(

VALUE(MID(SUBSTITUTE(A54,".",REPT(" ",6)),{1,8,15,22},{3,5,7,9}))<256,

VALUE(MID(SUBSTITUTE(A54,".",REPT(" ",6)),{1,8,15,22},{3,5,7,9}))=0),FALSE)


All times are GMT +1. The time now is 09:50 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com