List all valid IPv6 addresses. Following are the rules – 1. 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. 2. Leading 0s can be omitted. Hence, 00A6 can be written as A6 3. Double colons (::) can be used in place of a series of zeros. For example, IPv6 address CD34:0:0:0:0:0:0:A4 can be written as CD34::A4. 4. Double colons can be used only once in an IP address.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 355
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Validate IPv6 Address Format with Excel
Excel solution 1 for Validate IPv6 Address Format, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(t,
TEXTSPLIT(
a,
,
":"
),
y,
t="",
c,
CONCAT(
SORT(
IF(
y,
" ",
1
)
),
1
),
IFS(OR(y*(c=TRIM(
c
)),
LEN(
c
)=9)*AND(
HEX2DEC(
t
)<4^8
),
a)))),
3)Excel solution 2 for Validate IPv6 Address Format, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,
LAMBDA(a,
LET(t,
TEXTSPLIT(
a,
,
":"
),
y,
t="",
c,
CONCAT(
SORT(
IF(
y,
" ",
1
)
),
1
),
IFS(OR(y*(c=TRIM(
c
)),
LEN(
c
)=9)*AND(
HEX2DEC(
t
)<4^8
),
a)))),
3)Excel solution 3 for Validate IPv6 Address Format, proposed by John V.:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
x,
,
":"
),
c,
LEN(
x
)-LEN(
SUBSTITUTE(
x,
{"::",
":::"},
)
),
IFS(
AND(
HEX2DEC(
t
)<4^8,
IF(
MAX(
c
),
c<3,
ROWS(
t
)=8
)
),
x
)
)
)
),
2
)Excel solution 4 for Validate IPv6 Address Format, proposed by محمد حلمي:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(a,
LET(x,
TEXTSPLIT(
a,
,
":"
),
j,
LEN(
x
),
h,
HEX2DEC(
x
),
AND(j<5,
SUM(--(j=0))<2,
IFERROR(
FIND(
"::",
a
)^0+h,
NOT(
ISERR(
h
)
)*ROWS(
x
)=8
))))))Excel solution 5 for Validate IPv6 Address Format, proposed by Kris Jaganah:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
TEXTSPLIT(
x,
,
{":",
"::"}
),
IFS(MIN(
--ISNUMBER(
MAP(
a,
LAMBDA(
x,
HEX2DEC(
x
)
)
)
)
)*MIN(--(LEN(
a
)<5))*((ROWS(
a
)=8)+(IFERROR(
FIND(
"::",
x
)>0,
0
))*(MIN(--(""<>a)))),
x)))),
3)Excel solution 6 for Validate IPv6 Address Format, proposed by Timothée BLIOT:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
REGEXEXTRACT(
x,
"([0-9A-F]{1,4})((:(?1)){7}|(?!(.*:){8})(:(?1))*:(:(?1))+)"
)
)
),
3
)Excel solution 7 for Validate IPv6 Address Format, proposed by JvdV -:
=QUERY(
A2:A10,
"where UPPER(A) matches '[dA-F]{1,4}((:[dA-F]{1,4}){7}|(?!(.*:){8})(:[dA-F]{1,4})*:(:[dA-F]{1,4})+)'"
)Excel solution 8 for Validate IPv6 Address Format, proposed by Stefan Olsson:
=FILTER(
A2:A10 ,
REGEXMATCH(
A2:A10,
"^([[:xdigit:]]{1,4}(?::[[:xdigit:]]{1,4}){7}|::|:(?::[[:xdigit:]]{1,4}){1,6}|[[:xdigit:]]{1,4}:(?::[[:xdigit:]]{1,4}){1,5}|(?:[[:xdigit:]]{1,4}:){2}(?::[[:xdigit:]]{1,4}){1,4}|(?:[[:xdigit:]]{1,4}:){3}(?::[[:xdigit:]]{1,4}){1,3}|(?:[[:xdigit:]]{1,4}:){4}(?::[[:xdigit:]]{1,4}){1,2}|(?:[[:xdigit:]]{1,4}:){5}:[[:xdigit:]]{1,4}|(?:[[:xdigit:]]{1,4}:){1,6}:)$"
)
)Excel solution 9 for Validate IPv6 Address Format, proposed by Julien Lacaze:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(s,TEXTSPLIT(a,":"),
AND(ISNUMBER(HEX2DEC(s)))*AND(5>LEN(s))*ISERROR(FIND(":::",a))*IF(ISERROR(FIND("::",a)),(COLUMNS(s)=8),TRUE)))))Excel solution 10 for Validate IPv6 Address Format, proposed by Abdelrahman Omer, MBA, PMP:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(z,
LET(a,
TEXTSPLIT(
z,
,
":"
),
d,
IF(
a="",
0,
a
),
c,
MAP(d,
LAMBDA(x,
LET(b,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
(SUM(--(b<"G"))=LEN(
x
))*(LEN(
x
)<=4)))),
IF(SUM(--(a=""))<=1,
IF(SUM(--(a=""))=0,
SUM(--(c=1))=8,
SUM(--(c=1))=COUNTA(
a
)))))))Excel solution 11 for Validate IPv6 Address Format, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(f;
LAMBDA(ip;
LET(p;
DIVIDIRTEXTO(
ip;
":"
);
v;
SUMA(--(p=""))<2;
d;
SI.ERROR(
HALLAR(
"::";
ip
);
0
);
c;
COLUMNAS(
p
);
n;
MAX(
LARGO(
p
)
)<5;
vc;
ESERROR(
DIVIDIRTEXTO(
ip;
{":";
0;
1;
2;
3;
4;
5;
6;
7;
8;
9;
"A";
"B";
"C";
"D";
"E";
"F"};
;
1
)
);
Y(
v;
n;
vc;
SI(
d;
1;
c=8
)
)));
FILTRAR(
A2:A10;
MAP(
A2:A10;
LAMBDA(
i;
f(
i
)
)
)
))Solving the challenge of Validate IPv6 Address Format with Python in Excel
Python in Excel solution 1 for Validate IPv6 Address Format, proposed by JvdV -:
import regex as re
[i for i in xl("A2:A10")[0] if re.match(r'([dA-F]{1,4})((:(?1)){7}|(?!(.*:){8})(?3)*:(?3)+)', i)]
https://regex101.com/r/g4i4Qu/1
For fun, an FILTERXML solution:
=FILTER(A2:A10,ISERR(FILTERXML(""&SUBSTITUTE(A2:A10&":1",":","")&"&&&