Ask Your Question
1

Calc: How to specify formula for custom data validity?

asked 2020-01-17 13:05:39 +0200

Lot gravatar image

Hello, I'm trying to create a form and would like to include validation for fields such as IPv4 addresses. The Data Validity panel offers a "Custom" option that accepts a "Formula" but there is no indication of what this formula can be, and the online manual ignores the option of "Custom" criteria altogether so... How to do it?

I suppose the ideal thing would be if the software supported some kind of general regular expression validation. Other things I'd like to validate are Ethernet MAC addresses, IPv6 addresses, and E-mail addresses, the latter two being complex even with general regular expressions, but a person can dream...

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-01-17 13:12:14 +0200

updated 2020-01-17 13:24:03 +0200

This is just any formula which returns a value that might be interpreted as true (non-0) or false (0). So you can e.g. use REGEX function with ISERROR function checking its return value...

Just make the formula check the value in the cell you are making validity in:

=NOT(ISERROR(REGEX(A1;"^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")))

or

=ISTEXT(REGEX(A1;"^(([01]?\d{1,2}|2[0-4]\d|25[0-6])\.){3}([01]?\d{1,2}|2[0-4]\d|25[0-6])$"))
edit flag offensive delete link more

Comments

1

Thanks very much! This is just what I was looking for: not just the answer, but the logic behind it.

Lot gravatar imageLot ( 2020-01-19 13:03:05 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-01-17 13:05:39 +0200

Seen: 592 times

Last updated: Jan 17 '20