excel - Get decimal value of an ipv6 address -



excel - Get decimal value of an ipv6 address -

could help me formula calculate decimal value of ipv6 address?

i need set formula in vba create custom excel formula. know formula ipv4 , have read similar can not seem figure out. need able map ipv6 address range in ip2location csv.

i have excel ip functions should able start with.

=subnetipv4(ipv4,bits,offset) =isipv4(ipv4) ip address bits offset result result 10.11.12.13 26 0 10.11.12.0 true notes: macros must enabled ipv4 string representing ipv4 address in dotted decimal format bits integer (0 32) representing number of mask bits offset integer representing host address offset subnet using offset 0 retun subnet ip address using ipv4 of 255.255.255.255 , offset 0 retun mask of bits size =subnetipv6(ipv6,bits,offset) =isipv6(ipv6) ip address bits offset result result fe80::1dce:e8b3:1a14:2c3b 10 :: fe80:0:0:0:0:0:0:0 true notes: macros must enabled ipv6 string representing ipv6 address in standard format (leading 0s optional , :: works) bits integer (0 128) representing number of mask bits offset string representing host address offset subnet in standard format (leading 0s optional , :: works) using offset equivalent 0 (::, ::0, 0:0:0:0:0:0:0:0, etc.) retun subnet ip address using ipv6 of ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff , offset equivalent 0 retun mask of bits size function countstr(source string, target string) integer dim c, integer c = 0 if not ((source = "") or (target = "")) = 1 len(source) if mid(source, i, len(target)) = target c = c + 1 end if next end if countstr = c end function function subnetipv4(ipv4 string, bits integer, offset long) string dim a() string dim c, d, integer dim m long dim s string if ipv4 = "" goto invalidipv4 end if c = countstr(ipv4, ".") if c <> 3 goto invalidipv4 end if c = countstr(ipv4, "..") if c > 1 goto invalidipv4 end if if (left(ipv4, 1) = ".") or (right(ipv4, 1) = ".") goto invalidipv4 end if = split(ipv4, ".") if ubound(a) <> 3 goto invalidipv4 end if on error goto invalidipv4 = 0 3 if (len(a(i)) > 0) , (len(a(i)) < 4) a(i) = cint(a(i)) if (a(i) < 0) or (a(i) > 255) goto invalidipv4 end if else goto invalidipv4 end if next if (bits < 0) or (bits > 32) goto invalidipv4 end if c = bits mod 8 d = bits \ 8 if (bits <> 0) , (c = 0) c = 8 d = d - 1 end if m = 0 = 0 7 m = m * 2 if c > 0 m = m + 1 c = c - 1 end if next a(d) = cstr(clng(a(d)) , m) = d + 1 3 a(i) = "0" next if offset < 0 goto invalidipv4 end if m = 0 = 1 (32 - bits) m = m * 2 m = m + 1 next if offset > m goto invalidipv4 end if m = offset = 3 0 step -1 a(i) = a(i) + (m mod 256) m = m \ 256 next s = "" = 0 3 s = s + cstr(a(i)) + "." next s = left(s, len(s) - 1) subnetipv4 = s exit function invalidipv4: error (3) end function function isipv4(ipv4 string) boolean dim s string on error goto invalidipv4 s = subnetipv4(ipv4, 32, 0) isipv4 = true exit function invalidipv4: isipv4 = false end function function subnetipv6(ipv6 string, bits integer, offset string) string dim a() string dim c, d, integer dim m long dim s, t string if ipv6 = "" goto invalidipv6 end if c = countstr(ipv6, ":") if (c < 2) or (c > 8) goto invalidipv6 end if d = countstr(ipv6, "::") if d > 1 goto invalidipv6 end if if (left(ipv6, 1) = ":") , (not (left(ipv6, 2) = "::")) goto invalidipv6 end if if (right(ipv6, 1) = ":") , (not (right(ipv6, 2) = "::")) goto invalidipv6 end if s = ipv6 if d = 1 if left(s, 2) = "::" s = "0" + s end if if right(s, 2) = "::" s = s + "0" end if t = ":" = c 7 t = t + "0:" next s = replace(s, "::", t) end if = split(s, ":") if ubound(a) <> 7 goto invalidipv6 end if on error goto invalidipv6 = 0 7 if (len(a(i)) > 0) , (len(a(i)) < 5) a(i) = worksheetfunction.hex2dec(a(i)) else goto invalidipv6 end if next if (bits < 0) or (bits > 128) goto invalidipv6 end if c = bits mod 16 d = bits \ 16 if (bits <> 0) , (c = 0) c = 16 d = d - 1 end if m = 0 = 0 15 m = m * 2 if c > 0 m = m + 1 c = c - 1 end if next a(d) = cstr(clng(a(d)) , m) = d + 1 7 a(i) = "0" next if offset = "" goto invalidipv6 end if c = countstr(offset, ":") if (c < 2) or (c > 8) goto invalidipv6 end if d = countstr(offset, "::") if d > 1 goto invalidipv6 end if if (left(offset, 1) = ":") , (not (left(offset, 2) = "::")) goto invalidipv6 end if if (right(offset, 1) = ":") , (not (right(offset, 2) = "::")) goto invalidipv6 end if s = offset if d = 1 if left(s, 2) = "::" s = "0" + s end if if right(s, 2) = "::" s = s + "0" end if t = ":" = c 7 t = t + "0:" next s = replace(s, "::", t) end if b = split(s, ":") if ubound(b) <> 7 goto invalidipv6 end if on error goto invalidipv6 = 0 7 if (len(b(i)) > 0) , (len(b(i)) < 5) b(i) = worksheetfunction.hex2dec(b(i)) else goto invalidipv6 end if next c = bits mod 16 d = bits \ 16 if (bits <> 0) , (c = 0) c = 16 d = d - 1 end if m = 0 = 0 15 m = m * 2 if c > 0 m = m + 1 c = c - 1 end if next = 0 d - 1 if b(i) <> "0" goto invalidipv6 end if next if b(d) <> cstr(clng(b(d)) , m) goto invalidipv6 end if = 7 d step -1 a(i) = cstr(clng(a(i)) + clng(b(i))) next s = "" = 0 7 s = s + worksheetfunction.dec2hex(a(i)) + ":" next s = left(s, len(s) - 1) subnetipv6 = s exit function invalidipv6: error (3) end function function isipv6(ipv6 string) boolean dim s string on error goto invalidipv6 s = subnetipv6(ipv6, 128, "::") isipv6 = true exit function invalidipv6: isipv6 = false end function

excel decimal calculator ipv6

Comments

Post a Comment

Popular posts from this blog

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* -

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* - i've inherited doc publishing process takes docbook , builds html , pdf output, using apache fop both. project had been started, never completed. lots of tweaking, i've able of doc sets build (20+), except handful, , fail severe code 1 of these 2 conditions: "fo:flow" missing kid elements. required content model: marker* "fo:block" not valid kid of "fo:root" the xml output comes out of docbook , docbook-fop checks out well-formed. the fop error in 1 book, "endecagloss", example, points end of string: <fo:flow flow-name="xsl-region-body" start-indent="4pc" end-indent="0pt"/> here’s log output in case: [java] severe: javax.xml.transform.transformerexception: file:/scratch/publishing/hudson/jobs/build-main-endeca-documentation/workspace/serverdoc/w...
Read more

mediawiki - How do I insert tables inside infoboxes on Wikia pages? -

mediawiki - How do I insert tables inside infoboxes on Wikia pages? - i want insert tables in infoboxes. example, have proteinbox i've developed using infobox template (http://health-and-medicine.wikia.com/wiki/template:proteinbox), , fasta field big fit comfortably in proteinbox. hence collapsible table within box. this tried: {{infobox | row 1 = {| class = "mw-collapsible mw-collapsed wikitable" |- | <!-- table content --> |} }} your problem is, putting nested table within template. pipe characters ( | ) in table syntax collides pipe usage in templates. the commonly used hack around create template called template:! , containing pipe character, , utilize when need set tables, parser functions or other stuff using pipe characters, within templates. table (with every | replaced {{!}} ): {{{!}} {{!}}- {{!}} a1 {{!}} b1 {{!}}- {{!}} a2 {{!}} b2 {{!}}} ...the equivalent of {| |- | a1 | b1 |- | a2 | b2 |} furthermore, hav...
Read more

Local Service User Logged into Windows -

Local Service User Logged into Windows - i created windows service tracks app server user connected through load balancer. it retrieves user logged windows on machine, cross references active directory (ad) business relationship homecoming first name, lastly name , e-mail (for notification through website created project) there 1 scheme on network users remote system, vista system, reason returning values "local service" logged machine, it's weird , security guys impossible "local service" business relationship logged windows. here how user logged windows managementscope ms = new managementscope("\\\\.\\root\\cimv2"); objectquery query = new objectquery("select * win32_computersystem"); managementobjectsearcher searcher = new managementobjectsearcher(ms, query); foreach (managementobject mo in searcher.get()) { _username = mo["username"].tostring(); } // remove domain part username string[] usernameparts = _...
Read more