Excel Formula to validate valid IP and valid URL in librecalc excel sheet

Question

Excel formulas to validate valid IP and valid URL in librecalc excel sheet.

For Valid IP got one formula as below,

=AND((LEN(A1)-LEN(SUBSTITUTE(A1,".","")))=3,ISNUMBER(SUBSTITUTE(A1,".","")+0))

But this command just checks if three dots are there or not. But it will not check the numbers before the first dot or the numbers after last dot.

Now wrt valid url please help to provide macro to say valid url.

I found the formula below but it is not working as expected,

=IF(OR(LEFT(B90, 7) = "http://", LEFT(B90, 8) = "https://"), "Valid URL", "Not a valid URL")

Please help to resolve this issue, Thank You.

Answer 1

Use the REGEX spreadsheet function. Here is a Calc formula to check IP addresses.

=IF(ISNA(REGEX(A1;"^([:digit:]{1,3}\.){3}[:digit:]{1,3}"));"invalid";"valid")

A	B
.345.2.101	invalid
12.345.2.101	valid
12.34A5.2.101	invalid
12.345.2	invalid

You can write a similar formula for URLs.