IPv6 address is represented as x:x:x:x:x:x:x:x (total 8 x) where x consists of 1 to 4 Hexadecimal digits. Following rules to be followed to shorted an IPv6 address – 1. Leading 0s should be omitted. Hence, 00A6 should be written as A6. 2. Double colons (::) should be used in place of a series of contiguous zeros. For example, IPv6 address CD34:0000:0000:0000:0000:0000:0000:A4 can be written as CD34::A4. 3. Double colons should be used only once in an IP address. Since, we are looking at shortest possible IPv6, hence double colon in this case should be used where more number of series of contiguous 0s are there. CD34:0000:0000:2AB6:0000:0000:0000:A4 can be written as CD34:0:0:2AB6::A4 not as CD34::2AB6:0:0:0:A4.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 381
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Shorten IPv6 with Rules with Power Query
Power Query solution 1 for Shorten IPv6 with Rules, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"IPv6 Addresses", type text}, {"Expected Answer", type text}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each [
l = List.Transform(
Text.Split([IPv6 Addresses], ":"),
(x) => if Text.Remove(x, "0") = "" then "0" else Text.TrimStart(x, "0")
),
t = Table.Group(
Table.AddIndexColumn(Table.FromList(l, Splitter.SplitByNothing()), "i"),
{"Column1"},
{
{"i", (x) => x[i]},
{"c", (x) => Table.RowCount(x), type number},
{"f", (x) => Text.Remove(x[Column1]{0}, "0") = "", type logical}
},
GroupKind.Local
),
s = Table.Sort(
Table.SelectRows(t, (x) => x[f]),
{{"c", Order.Descending}, {(y) => List.Sum(y[i]), Order.Ascending}}
)[i]{0},
r =
let
a = Text.Combine(
try List.InsertRange(List.RemoveRange(l, s{0}, List.Count(s)), s{0}, {""}) otherwise l,
":"
)
in
if a = "" then "::" else if Text.End(a, 1) = ":" then a & ":" else a
][r]
)
in
#"Added Custom"Solving the challenge of Shorten IPv6 with Rules with Excel
Excel solution 1 for Shorten IPv6 with Rules, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A11,
LAMBDA(
a,
LET(
c,
SUBSTITUTE(
TEXTJOIN(
" ",
0,
,
DEC2HEX(
HEX2DEC(
TEXTSPLIT(
a,
,
":"
)
)
),
),
REPT(
" 0",
SEQUENCE(
8
)
)&" ",
"::",
1
),
SUBSTITUTE(
TRIM(
XLOOKUP(
0,
LEN(
c
),
c,
,
1
)
),
" ",
":"
)
)
)
)Excel solution 2 for Shorten IPv6 with Rules, proposed by Rick Rothstein:
=MAP(
A2:A11,
LAMBDA(
z,
LET(
e,
SEQUENCE(
8
),
t,
TEXTJOIN(
":",
,
DEC2HEX(
HEX2DEC(
TEXTSPLIT(
z,
":"
)
)
)
),
h,
":"&t&":",
r,
REDUCE(
h,
e,
LAMBDA(
a,
x,
SUBSTITUTE(
a,
":0",
":",
1
)
)
),
m,
MAX(
LEN(
TEXTSPLIT(
r,
BASE(
SEQUENCE(
16,
,
0
),
16
)
)
)
),
IF(
ISERROR(
FIND(
":::",
r
)
),
t,
SUBSTITUTE(
SUBSTITUTE(
TRIM(
SUBSTITUTE(
REDUCE(
SUBSTITUTE(
r,
REPT(
":",
m
),
"|",
1
),
e,
LAMBDA(
a,
x,
SUBSTITUTE(
a,
"::",
":0:"
)
)
),
":",
" "
)
),
" ",
":"
),
"|",
"::"
)
)
)
)
)Excel solution 3 for Shorten IPv6 with Rules, proposed by John V.:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
t,
SUBSTITUTE(
TEXTJOIN(
" ",
0,
,
DEC2HEX(
HEX2DEC(
TEXTSPLIT(
x,
":"
)
)
),
),
" "&REPT(
"0 ",
ROW(
1:8
)
),
"::",
1
),
SUBSTITUTE(
TRIM(
@SORTBY(
t,
LEN(
t
)
)
),
" ",
":"
)
)
)
)Excel solution 4 for Shorten IPv6 with Rules, proposed by محمد حلمي:
=MAP(
A2:A11,
LAMBDA(
a,
LET(
D,
DEC2HEX(
HEX2DEC(
TEXTSPLIT(
a,
,
":"
)
)
),
e,
SCAN(
,
D="0",
LAMBDA(
a,
d,
d*a+d
)
),
m,
MAX(
e
),
j,
XMATCH(
m,
e
),
TEXTJOIN(
":",
0,
IFS(
m=j,
"::",
m,
VSTACK(
TAKE(
D,
j-m
),
"",
IFERROR(
DROP(
D,
j
),
""
)
),
1,
D
)
)
)
)
)Excel solution 5 for Shorten IPv6 with Rules, proposed by Timothée BLIOT:
=MAP(
A2:A11,
LAMBDA(
z,
LET(
A,
REGEXREPLACE(
z,
"(b0+)(?=[0-9A-F]+)",
""
),
B,
REGEXEXTRACT(
A,
"(:?(?Excel solution 6 for Shorten IPv6 with Rules, proposed by JvdV -:
=MAP(A2:A11,
LAMBDA(s,
LET(d,
":",
t,
TEXTJOIN(
d,
0,
,
DEC2HEX(
HEX2DEC(
TEXTSPLIT(
s,
d
)
)
)
),
l,
SUBSTITUTE(
t,
REPT(
d&0,
9-ROW(
1:8
)
),
d,
1
),
z,
XLOOKUP(
1,
N(
t<>l
),
l,
t
),
MID(z,
2-(z=d),
38)&REPT(
d,
RIGHT(
z
)=d
))))Excel solution 7 for Shorten IPv6 with Rules, proposed by Edwin Tisnado:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
f,
":",
t,
DEC2HEX(
HEX2DEC(
TEXTSPLIT(
x,
,
f
)
)
),
j,
MAX(
SCAN(
,
t="0",
LAMBDA(
x,
y,
x*y+y
)
)
),
i,
SUBSTITUTE(
TEXTJOIN(
f,
,
t
),
REPT(
":0",
j
),
f,
1
),
IF(
j=8,
"::",
IF(
RIGHT(
i
)=f,
i&f,
i
)
)
)
)
)Solving the challenge of Shorten IPv6 with Rules with Python in Excel
Python in Excel solution 1 for Shorten IPv6 with Rules, proposed by JvdV -:
import re
[re.sub(r':?(b0(:0)+)(?!.*:1:0):?','::',re.sub(r'b0+(w+)',r'1',s),1)for s in xl("A2:A11")[0]]Solving the challenge of Shorten IPv6 with Rules with R
R solution 1 for Shorten IPv6 with Rules, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/381 IPv6 Shortening.xlsx", range = "A1:A11")
test = read_excel("Excel/381 IPv6 Shortening.xlsx", range = "B1:B11")
shorten_ipv6 = function(ipv6) {
blocks = str_split(ipv6, ":")[[1]] %>%
str_replace( "^0+", "") %>%
str_replace("^$", "0")
zeros = blocks %>%
str_detect("^0+$") %>%
which() %>%
unlist() %>%
data.frame(x = .) %>%
mutate(group = cumsum(x - lag(x, default = 0) > 1)) %>%
group_by(group) %>%
mutate(n = n(),
min_index = min(x),
max_index = max(x)) %>%
ungroup() %>%
arrange(desc(n), group) %>%
slice(1)
first_zero = zeros$min_index
last_zero = zeros$max_index
block_df = data.frame(blocks = blocks,
index = 1:length(blocks),
stringsAsFactors = FALSE)
block_df$blocks[block_df$index >= first_zero & block_df$index <= last_zero] = ""
result = block_df$blocks %>%
str_c(collapse = ":") %>%
str_replace(., ":{2,}", "::")
return(result)
}
result = input %>%
mutate(short = map_chr(`IPv6 Addresses`, shorten_ipv6))
&&&