Ask Your Question
0

can I turn an IP address into a number in calc?

asked 2019-12-11 18:23:14 +0200

clos911 gravatar image

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.

edit retag flag offensive close merge delete

Comments

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.

Opaque gravatar imageOpaque ( 2019-12-11 18:35:18 +0200 )edit

Just to elaborate on @Opaque's comment: What is the purpose of your conditional format?

  • Do you want to mark valid/invalid IPv4 addresses?
  • Tagging the network and broadcast addresses of a subnet?
  • Something else?
keme gravatar imagekeme ( 2019-12-11 18:59:26 +0200 )edit

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..

clos911 gravatar imageclos911 ( 2019-12-11 19:06:35 +0200 )edit

Did not get that ...

Opaque gravatar imageOpaque ( 2019-12-11 19:15:15 +0200 )edit

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.

keme gravatar imagekeme ( 2019-12-11 20:19:07 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-12 08:07:39 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2019-12-11 21:07:53 +0200

Lupp gravatar image

updated 2019-12-11 21:39:27 +0200

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.

edit flag offensive delete link more

Comments

@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)

Opaque gravatar imageOpaque ( 2019-12-11 23:09:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-12-11 18:23:14 +0200

Seen: 290 times

Last updated: Dec 11 '19