I have a column with conditional formatting for min and max, but it does not apply the formats for IP addresses like 192.168.0.1 or 10.11.0.123, it does work for regular numbers. is there anyway to get the conditional formatting to apply to ip addresses or is there a way to turn the ip addresses into numbers? when I copy an ip addressand do a special paste for number only nothing gets pasted, doing a text only paste the IP address does paste. any help is appreciated.
Which condition would you like to apply to an IP address? (min and max of a complete IP makes no sense at all, maybe max IP address used in a subnet is of interest). BTW: An Ip address isn’t a number, thus you get the paste behavior observed.
Just to elaborate on @anon73440385’s comment:
What is the purpose of your conditional format?
- Do you want to mark valid/invalid IPv4 addresses?
- Tagging the
networkandbroadcastaddresses of a subnet? - Something else?
using a 3 color scale with the min and middle being the same color blue to identify the valid addresses, and the max to identify addresses that are out of range in red… might be unconventional but it works…
Did not get that …
The obvious (but perhaps a bit clunky) solution is to use 4 columns, one for each “byte segment”. With that you can have proper numbers, so you can probably use your working solution, more or less unmodified.
BTW: An Ip address isn’t a number, thus you get the paste behavior observed.
Just to be pedantic: an IP address in fact is a number (32-bit for IPv4), but its common representation isn’t.
I surely didn’t clearly understand the context, but …
Disregarding broadcasting and whatever specifics you can get unabbreviated versions of the dot-decimal ipV4 addresses by
=REGEX(REGEX(A1;"(?<=(^|\.))(\d)(?=(\.|$))";"0$0";"g");"(?<=(^|\.))(\d\d)(?=(\.|$))";"0$0";"g")
in LibreOffice V6.2 or higher. As always with g-adic numbers the omission of leading zeros spoils lexicographic sorting (comparison under inequality). The reinsertion of leading zeros done by the above formula fo an ipV4 in cell A1 will make the problem vanish.
===Edit1 ===
You may prefer the formula
{=TEXTJOIN(".";0;TEXT(VALUE(REGEX(A1;"\d+";;ROW(INDIRECT("a1:a4"))));"000"))}
entered for array-evaluation for the same purpose. It stresses the RegEx features less.
@Lupp Don’t take me serious - but this question and solution reminds me to the phrase “We have a solution, now looking for a matching problem” (obviously my problem)