IPv4 Data validity possible?

[bmmcwhirt]

Is it possible, and if so how, to set up data validity for IPv4 addresses to force them to be entered as follows: ###.###.###.### (always 3 digits for each octet)

192.168.000.001 VALID
192.168.00.01 INVALID
192.168.0.1 INVALID

[Villeroy]

=ISNUMBER(SEARCH("^[0-9]{3}\.[0-9]{3}\.[0-9]{3}\.[0-9]{3}$";A1))
but that does not prevent invalid IPs such as 999.999.999.999

[bmmcwhirt]

=ISNUMBER(SEARCH("^[0-9]{3}\.[0-9]{3}\.[0-9]{3}\.[0-9]{3}$";A1))
but that does not prevent invalid IPs such as 999.999.999.999

In the criteria tab, what settings do I need for allow?

[Villeroy]

If you know the allowed IP range, you can try this:
Allow integer values between 0 and 255 for 4 cells in a row and then concatenate the IP like this:
=CONCATENATE(TEXT(A1;"000");".";TEXT(B1;"000");".";TEXT(C1;"000");".";TEXT(D1;"000"))

[bmmcwhirt]

If you know the allowed IP range, you can try this:
Allow integer values between 0 and 255 for 4 cells in a row and then concatenate the IP like this:
=CONCATENATE(TEXT(A1;"000");".";TEXT(B1;"000");".";TEXT(C1;"000");".";TEXT(D1;"000"))

No, this is all going into one cell. When I edit the Data Validity though I only have Allow: All values, whole numbers, decimal, date, time, cell range, list, text length.

There is not custom or anything that I can see to allow me to do this, but it seems like there should be a way.

[RusselB]

Villeroy's suggestions do not require you to use Data Validation.
If you know the IP range (and don't mind doing what could be a lot of typing), then you could put the list of valid IP addresses into a list or cell range.
If the 2nd of Villeroy's formulas looks interesting, except for the fact that it uses multiple cell addresses, you could split the IP address into 4 sections using combinations of SEARCH, LEFT, MID and/or RIGHT functions into helper cells.
If you don't want to use helper cells, then you could replace the calculated sections (using the same methods as for getting helper cells) directly into Villeroy's formula.

[John_Ha]

Presumably it could be done with a Regular Expression - Documentation/How Tos/Regular Expressions in Calc.

Ms Google quickly found thousands of sites like Validating IPv4 addresses with regexp

[bmmcwhirt]

It has to be done in Data Validity as I need to have an alert pop up when someone enters an invalid format.

[RusselB]

I think, in that case, your only option is to make a list either in the Data Validity dialog or in a cell range and pick the appropriate Data Validity option.

[Villeroy]

Conditional formatting as "alert pop up"

[bmmcwhirt]

That's good but isn't usable for me as it doesn't actually stop them from entering wrong data. At best it can delete the data after they enter it but thanks to the US education system they will just go on as if it was entered correctly. I would use macro scripting but we have all that disabled for security reasons.

[RusselB]

That's good but isn't usable for me as it doesn't actually stop them from entering wrong data. At best it can delete the data after they enter it but thanks to the US education system they will just go on as if it was entered correctly. I would use macro scripting but we have all that disabled for security reasons.

US Education System should (and probably does) have employees in the IT department to handle these kinds of restrictions.
For something that will prevent the immediate entry of an invalid IP address, that is the department that should be handling this, not a user working with a spreadsheet.

[Villeroy]

If anyone is interested and authorized to use macros:
Call menu:Tools>Options>Security>[Macro Security]
Do NOT turn off macro security but add a trusted directory.
Save the attached database document to the trusted directory.
Open the document and then any of the 2 input forms.

[Villeroy]

And another one for those who are interested in solutions with no macros.

[Villeroy]

If you need a list of unique IP strings in a spreadsheet, save a query within the 2nd database

Code: Select all

SELECT DISTINCT "IP" FROM "qConcat_IPv4" 

Register the database under some name in Tools>Options>Base>Databases
Open a Calc document
Get the data source window (F4 should do)
Browse your registered database --> Queries and drag your query into the sheet.

After the database has been changed, menu:Data>Refresh (cell cursor in range) will update the import range.

A query to get the IPs where the first byte equals 182

Code: Select all

SELECT "IP" FROM "qConcat_IPv4" WHERE "B1"=182

A query which prompts you to enter the first characters of an IP string:

Code: Select all

SELECT "IP" FROM "qConcat_IPv4" WHERE "IP" LIKE :IP_starts_with || '%'

The last 5 entries

Code: Select all

SELECT "IP" FROM "qConcat_IPv4" ORDER BY "ID" DESC LIMIT 5