This is first part of 2 parts Italian Fiscal Code (Codice Fiscale) generation challenges. Given two parts of name, generate the alphabets in capital letters as per following rule. First part – From Surname (i.e. Last Name) 1. If name contains >=3 consonants, then first 3 consonants are used in the same order as they appear Ex. Roberto -> RBR 2. If name contains <3 consonants, use all consonants first and the vowels replace missing characters in the same order as they appear Ex. Rob ->RBO, Taou ->TAO, Uae -> UAE 3. If name itself has less than 3 alphabets, then X will appear after first consonant and then vowel. Ex. Ro -> ROX, U -> UXX Second part – From First Name 1. If name contains = 3 consonants, then these 3 consonants are used in the same order as they appear Ex. Vijay ->VJY 2. If name contains > 3 consonants, then first, third and fourth consonant are used in the same order as they appear Ex. Smith -> STH 3. Same as point 2 of Surname 4. Same as point 3 of Surname
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 102
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Italian Fiscal Code with Power Query
Power Query solution 1 for Generate Italian Fiscal Code, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Y = (p, n) =>
let
u = Text.Upper(p),
a = Text.Combine(Text.SplitAny(u, "AEIOU"))
in
Text.Start(
if Text.Length(a) > n then
Text.RemoveRange(a, 1, 1)
else
a & Text.Combine(Text.SplitAny(u, a)) & "XX",
3
),
ACode = Table.AddColumn(
Source,
"Code",
each Y(Text.AfterDelimiter([Names], " "), 20) & " " & Y(Text.BeforeDelimiter([Names], " "), 3)
)
in
ACodePower Query solution 2 for Generate Italian Fiscal Code, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Uppercased = Table.TransformColumns(Source, {{"Names", Text.Upper, type text}}),
Vocales = {"A", "E", "I", "O", "U"},
Consonantes = List.RemoveItems({"A" .. "Z"}, Vocales),
Nombres = Table.AddColumn(
Uppercased,
"Code",
each
let
a = [Names],
b = Text.Split(a, " "),
c = List.RemoveItems(Text.ToList(b{0}), Vocales),
y = List.RemoveItems(Text.ToList(b{0}), Consonantes),
d =
if List.Count(c) = 3 then
Text.Combine(c, "")
else if List.Count(c) > 3 then
Text.Combine({c{0}, c{2}, c{3}}, "")
else if Text.Length(b{0}) < 3 then
Text.PadEnd(Text.Combine(c & y, ""), 3, "X")
else
Text.Combine(List.FirstN(c & y, 3)),
c1 = List.RemoveItems(Text.ToList(b{1}), Vocales),
y1 = List.RemoveItems(Text.ToList(b{1}), Consonantes),
d1 =
if List.Count(c1) >= 3 then
Text.Combine(List.FirstN(c1, 3), "")
else if Text.Length(b{1}) < 3 then
Text.PadEnd(Text.Combine(c1 & y1, ""), 3, "X")
else
Text.Combine(List.FirstN(c1 & y1, 3))
in
d1 & " " & d
)
in
NombresPower Query solution 3 for Generate Italian Fiscal Code, proposed by Brian Julius:
BoCode = Table.AlternateRows(MyCode, 1, 3, 1)Power Query solution 4 for Generate Italian Fiscal Code, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "CodiceFiscale"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Code",
each [
A = Text.Upper([Names]),
a = Text.AfterDelimiter(A, " "),
b = Text.BeforeDelimiter(A, " "),
c = {"A", "E", "I", "O", "U"},
// Surname
d = Text.Start(Text.Remove(a, c) & Text.Select(a, c) & "XX", 3) & " ",
e = Text.Remove(b, c),
// First Name
f = if Text.Length(e) > 3 then Text.Start(e, 1) & Text.Range(e, 2, 2) else e,
g = Text.Start(f & Text.Select(b, c) & "XX", 3),
h = d & g
][h]
)
in
#"Added Custom"Power Query solution 5 for Generate Italian Fiscal Code, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Code",
each
let
t = Text.Split(Text.Upper([Names]), " "),
t0 = Text.ToList(t{0}),
t1 = Text.ToList(t{1}),
v = {"A", "E", "I", "O", "U"},
o = List.RemoveItems({"A" .. "Z"}, v)
in
Text.Combine(
List.Combine(
{
[
a = List.RemoveItems(t1, v),
b = List.RemoveItems(t1, o),
c = List.Repeat({"X"}, Text.Length([Names])),
d = List.FirstN(a & b & c, 3)
][d],
{" "},
[
a = List.RemoveItems(t0, v),
b = List.RemoveItems(t0, o),
c = List.Repeat({"X"}, Text.Length([Names])),
d = if List.Count(a) > 3 then List.RemoveRange(a, 1, 1) else a,
e = List.FirstN(d & b & c, 3)
][e]
}
)
)
)
in
ResultPower Query solution 7 for Generate Italian Fiscal Code, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Code",
each [
x = {"A", "E", "I", "O", "U"},
a = Text.Split(Text.Upper([Names]), " "),
b = Text.Start(Text.PadEnd(Text.Remove(a{0}, x) & Text.Select(a{0}, x), 3, "X"), 3),
c = Text.Start(Text.PadEnd(Text.Remove(a{1}, x) & Text.Select(a{1}, x), 3, "X"), 3),
d = c & " " & b
][d]
)
in
ResultSolving the challenge of Generate Italian Fiscal Code with Excel
Excel solution 1 for Generate Italian Fiscal Code, proposed by Bo Rydobon 🇹🇭:
=LET(
y,
LAMBDA(
a,
n,
LET(
c,
CHAR(
SEQUENCE(
26,
,
65
)
),
v,
{"A",
"E",
"I",
"O",
"U"},
b,
CONCAT(
TEXTSPLIT(
a,
v
)
),
LEFT(
CONCAT(
REPLACE(
b,
2,
LEN(
b
)>n,
),
TEXTSPLIT(
a,
FILTER(
c,
ISNA(
XMATCH(
c,
v
)
)
)
),
"XX"
),
3
)
)
),
MAP(
UPPER(
A2:A11
),
LAMBDA(
z,
y(
TEXTAFTER(
z,
" "
),
9
)&" "&y(
TEXTBEFORE(
z,
" "
),
3
)
)
)
)Excel solution 2 for Generate Italian Fiscal Code, proposed by Bo Rydobon 🇹🇭:
=LET(
y,
LAMBDA(
a,
n,
LET(
v,
{"A",
"E",
"I",
"O",
"U"},
b,
CONCAT(
TEXTSPLIT(
a,
v
)
),
LEFT(
CONCAT(
REPLACE(
b,
2,
LEN(
b
)>n,
),
TEXTSPLIT(
a,
MID(
b&0,
SEQUENCE(
LEN(
b
)+1
),
1
)
),
"XX"
),
3
)
)
),
MAP(
UPPER(
A2:A11
),
LAMBDA(
z,
y(
TEXTAFTER(
z,
" "
),
9
)&" "&y(
TEXTBEFORE(
z,
" "
),
3
)
)
)
)Excel solution 3 for Generate Italian Fiscal Code, proposed by Bo Rydobon 🇹🇭:
=LET(
Y,
LAMBDA(
a,
x,
LET(
m,
MID(
UPPER(
a
),
SEQUENCE(
LEN(
a
)
),
1
),
n,
-ISERR(
FIND(
m,
"AEIOU"
)
),
s,
SORTBY(
m,
n
),
LEFT(
REPLACE(
CONCAT(
s
),
2,
SUM(
-n
)>x,
)&"XX",
3
)
)
),
MAP(
A2:A11,
LAMBDA(
z,
Y(
TEXTAFTER(
z,
" "
),
9
)&" "&Y(
TEXTBEFORE(
z,
" "
),
3
)
)
)
)Excel solution 4 for Generate Italian Fiscal Code, proposed by Rick Rothstein:
=MAP(A2:A11,
LAMBDA(z,
LET(v,
{"A",
"E",
"I",
"O",
"U"},
f,
LAMBDA(t,
fl,
LET(gn,
UPPER(
IF(
fl=1,
TEXTBEFORE(
t,
" "
),
TEXTAFTER(
t,
" "
)
)
),
m,
MID(
gn,
SEQUENCE(
LEN(
gn
)
),
1
),
c,
CONCAT(
TEXTSPLIT(
gn,
v
)
),
LEFT(LEFT(REPLACE(CONCAT(
c
),
2,
0+(fl=1)*(LEN(
c
)>3),
""),
3)&
LEFT(
LEFT(
CONCAT(
IF(
ISNUMBER(
FIND(
m,
"AEIOU"
)
),
m,
""
)
),
3
)&
"XXX",
3
),
3))),
f(
z,
2
)&" "&f(
z,
1
))))Excel solution 5 for Generate Italian Fiscal Code, proposed by John V.:
=LET(f,
LAMBDA(n,
t,
LET(v,
{"A";"E";"I";"O";"U"},
l,
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
),
x,
XLOOKUP(
l,
v,
v
),
e,
ISNA(
x
),
CONCAT(INDEX(TOCOL(
VSTACK(
IF(
e,
l,
z
),
x,
{"X";"X"}
),
2
),
{1;2;3}+(SUM(
--e
)>3)*t*{0;1;1})))),
MAP(
UPPER(
A2:A11
),
LAMBDA(
x,
f(
TEXTAFTER(
x,
" "
),
)&" "&f(
TEXTBEFORE(
x,
" "
),
1
)
)
))Excel solution 6 for Generate Italian Fiscal Code, proposed by محمد حلمي:
=MAP(
A2:A11,
LAMBDA(
l,
LET(
w,
LAMBDA(
a,
LET(
s,
TEXTSPLIT(
UPPER(
l
),
,
" "
),
d,
IF(
a,
@DROP(
s,
1
),
@TAKE(
s,
1
)
),
v,
MID(
d,
SEQUENCE(
20
),
1
),
g,
LAMBDA(
z,
[m],
CONCAT(
m,
IF(
z-ISERR(
FIND(
v,
"AIEOU"
)
),
v,
""
)
)
),
e,
g(
0
),
i,
LEN(
e
),
k,
g(
1,
e
),
y,
d&"XX",
n,
LEN(
d
)<3,
LEFT(
CONCAT(
IF(
a,
IFS(
i>2,
e,
n,
y,
1,
k
),
IFS(
i=3,
e,
i>3,
MID(
e,
{1;3;4},
1
),
n,
y,
1,
k
)
)
),
3
)
)
),
w(
1
)&" "&w(
0
)
)
)
)Excel solution 7 for Generate Italian Fiscal Code, proposed by محمد حلمي:
=MAP(
A2:A11,
LAMBDA(
l,
LET(
w,
LAMBDA(
a,
LET(
s,
TEXTSPLIT(
UPPER(
l
),
,
" "
),
d,
& IF(
a,
@DROP(
s,
1
),
@TAKE(
s,
1
)
),
v,
MID(
d,
SEQUENCE(
20
),
1
),
g,
LAMBDA(
z,
[m],
CONCAT(
m,
IF(
z-ISERR(
FIND(
v,
"AIEOU"
)
),
v,
""
)
)
),
e,
g(
0
),
i,
LEN(
e
),
k,
g(
1,
e
),
y,
d&"XX",
n,
LEN(
d
)<3,
LEFT(
CONCAT(
IF(
a,
IFS(
i>2,
e,
n,
y,
1,
k
),
IFS(
i=3,
e,
i>3,
MID(
e,
{1;3;4},
1
),
n,
y,
1,
k
)
)
),
3
)
)
),
w(
1
)&" "&w(
0
)
)
)
)Excel solution 8 for Generate Italian Fiscal Code, proposed by محمد حلمي:
=UPPER(
MAP(
A2:A11,
LAMBDA(
a,
LET(
d,
TOCOL(
{65,
69,
73,
79,
85}+{0;32}
),
e,
TEXTAFTER(
a,
" "
),
ee,
TEXTBEFORE(
a,
" "
),
c,
CODE(
MID(
e,
SEQUENCE(
LEN(
e
)
),
1
)
),
cc,
CODE(
MID(
ee,
SEQUENCE(
LEN(
ee
)
),
1
)
),
v,
CONCAT(
CHAR(
TAKE(
SORTBY(
c,
ISNA(
XMATCH(
c,
d
)
),
-1
),
3
)
)
),
vv,
ISNA(
XMATCH(
cc,
d
)
),
r,
IF(
SUM(
--vv
)>3,
INDEX(
SORTBY(
cc,
vv,
-1
),
{1,
3,
4}
),
SORTBY(
cc,
vv,
-1
)
),
i,
CONCAT(
CHAR(
TAKE(
r,
3
)
)
),
IF(
LEN(
v
)<3,
v&REPT(
"X",
3-LEN(
v
)
),
v
)&" "&
IF(
LEN(
i
)<3,
i&REPT(
"X",
3-LEN(
"i"
)
),
i
)
)
)
)
)Excel solution 9 for Generate Italian Fiscal Code, proposed by محمد حلمي:
=UPPER(
MAP(
A2:A11,
LAMBDA(
a,
LET(
d,
TOCOL(
{65,
69,
73,
79,
85}+{0;32}
),
e,
TEXTAFTER(
a,
" "
),
ee,
TEXTBEFORE(
a,
" "
),
l,
LAMBDA(
x,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
),
c,
l(
e
),
cc,
l(
ee
),
v,
CONCAT(
CHAR(
TAKE(
SORTBY(
c,
ISNA(
XMATCH(
c,
d
)
),
-1
),
3
)
)
),
vv,
ISNA(
XMATCH(
cc,
d
)
),
r,
IF(
SUM(
--vv
)>3,
INDEX(
SORTBY(
cc,
vv,
-1
),
{1,
3,
4}
),
SORTBY(
cc,
vv,
-1
)
),
i,
CONCAT(
CHAR(
TAKE(
r,
3
)
)
),
z,
LEN(
v
),
y,
LEN(
i
),
IF(
z<3,
v&REPT(
"X",
3-z
),
v
)&" "&
IF(
y<3,
i&REPT(
"X",
3-y
),
i
)
)
)
)
)Excel solution 10 for Generate Italian Fiscal Code, proposed by Kris Jaganah:
=LET(a,
A2:A11,
b,
UPPER(
TEXTAFTER(
a,
" ",
1
)
),
c,
LEN(
b
),
d,
BYROW(
b,
LAMBDA(
p,
REDUCE(
p,
MID(
"AEIOU",
SEQUENCE(
5
),
1
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
)
)
),
e,
LEN(
d
),
f,
BYROW(
b,
LAMBDA(
p,
REDUCE(
p,
MID(
"BCDFGHJKLMNPQRSTVWXYZ",
SEQUENCE(
21
),
1
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
)
)
),
g,
MAP(b,
c,
d,
e,
f,
LAMBDA(v,
w,
x,
y,
z,
IF(AND(
w>=3,
y>=3
),
LEFT(
x,
3
),
IF(AND(
w>=3,
y<3
),
x&LEFT(
z,
3-LEN(
x
)
),
IF(w<3,
x&z&REPT("X",
(3-w))))))),
h,
UPPER(
TEXTBEFORE(
A2:A11,
" ",
1
)
),
i,
LEN(
h
),
j,
BYROW(
h,
LAMBDA(
p,
REDUCE(
p,
MID(
"AEIOU",
SEQUENCE(
5
),
1
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
)
)
),
k,
LEN(
j
),
l,
BYROW(
h,
LAMBDA(
p,
REDUCE(
p,
MID(
"BCDFGHJKLMNPQRSTVWXYZ",
SEQUENCE(
21
),
1
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
)
)
),
m,
MAP(h,
i,
j,
k,
l,
LAMBDA(v,
w,
x,
y,
z,
IF(y=3,
x,
IF(y>3,
LEFT(
x,
1
)&MID(
x,
3,
2
),
IF(AND(
w>=3,
y<3
),
x&LEFT(
z,
3-LEN(
x
)
),
IF(w<3,
x&z&REPT("X",
(3-w)))))))),
g&" "&m)Excel solution 11 for Generate Italian Fiscal Code, proposed by Julian Poeltl:
=MAP(
A2:A11,
LAMBDA(
N,
LET(
C,
{"a",
"e",
"i",
"o",
"u"},
F,
TEXTAFTER(
N,
" "
),
FS,
MID(
F,
SEQUENCE(
LEN(
F
)
),
1
),
T,
LEFT(
CONCAT(
SORTBY(
FS,
--ISNUMBER(
XMATCH(
FS,
C
)
)
)
),
3
),
FP,
T&REPT(
"X",
3-LEN(
T
)
),
S,
TEXTBEFORE(
N,
" "
),
SS,
MID(
S,
SEQUENCE(
LEN(
S
)
),
1
),
IC,
--NOT(
ISNUMBER(
XMATCH(
SS,
C
)
)
),
NC,
SUM(
IC
),
TT,
IFS(
NC=3,
CONCAT(
FILTER(
SS,
IC
)
),
NC>3,
CONCAT(
CHOOSEROWS(
FILTER(
SS,
IC
),
1,
3,
4
)
),
1,
LEFT(
CONCAT(
SORTBY(
SS,
--ISNUMBER(
XMATCH(
SS,
C
)
)
)
),
3
)
),
SP,
TT&REPT(
"X",
3-LEN(
TT
)
),
UPPER(
FP&" "&SP
)
)
)
)Excel solution 12 for Generate Italian Fiscal Code, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:A11,
_v,
{"A"; "E"; "I"; "O"; "U"},
_ch,
CHAR(
SEQUENCE(
26,
,
65
)
),
_co,
UNIQUE(
VSTACK(
_v,
_ch
),
,
1
),
_fd,
UPPER(
_d
),
_n1,
TEXTBEFORE(
_fd,
" "
),
_n2,
TEXTAFTER(
_fd,
" "
),
_e1,
LAMBDA(
a,
b,
SUBSTITUTE(
a,
b,
""
)
),
_e2,
LAMBDA(
x,
REDUCE(
x,
_v,
_e1
)
),
_e3,
LAMBDA(
x,
REDUCE(
x,
_co,
_e1
)
),
_f1,
LEFT(_e2(_n2) & _e3(_n2) & "XX",
3),
_f2,
LEFT(
REPLACE(_e2(_n1),
2,
LEN(_e2(_n1)) > 3,
"") & _e3(_n1) & "XX",
3
),
_r,
_f1 & " " & _f2,
_r
)Excel solution 13 for Generate Italian Fiscal Code, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A11,
LAMBDA(a,
LET(
v,
{"A"; "E"; "I"; "O"; "U"},
t1,
TEXTAFTER(
a,
" "
),
e1,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
e2,
LAMBDA(
x,
ISNA(
XMATCH(
x,
v
)
)
),
e3,
LAMBDA(
x,
FILTER(
x,
e2(
x
),
""
)
),
e4,
LAMBDA(
x,
FILTER(
x,
1 - e2(
x
),
""
)
),
e5,
LAMBDA(x,
e1(TEXTJOIN(
"",
1,
e3(
x
),
e4(
x
)
))),
e6,
LAMBDA(
x,
y,
IFERROR(
INDEX(
x,
y
),
"X"
)
& ),
s1,
e1(t1),
j1,
e5(s1),
t2,
TEXTBEFORE(
a,
" "
),
s2,
e1(t2),
j2,
e5(s2),
cnt,
ROWS(e3(s2)),
f1,
e6(j1,
{1; 2; 3}),
f2,
IF(cnt > 3,
e6(j2,
{1; 3; 4}),
e6(j2,
{1; 2; 3})),
r,
UPPER(
CONCAT(
f1,
" ",
f2
)
),
r
)
)
)Excel solution 14 for Generate Italian Fiscal Code, proposed by Timothée BLIOT:
=MAP(
A2:A11,
LAMBDA(
z,
LET(
A,
UPPER(
TEXTSPLIT(
z,
" "
)
),
B,
{"A",
"E",
"I",
"O",
"U"},
AF,
MID(
INDEX(
A,
1
),
SEQUENCE(
LEN(
INDEX(
A,
1
)
)
),
1
),
AL,
MID(
INDEX(
A,
2
),
SEQUENCE(
LEN(
INDEX(
A,
2
)
)
),
1
),
BF,
MAP(
AF,
LAMBDA(
x,
--NOT(
ISNUMBER(
XMATCH(
x,
B
)
)
)
)
),
BL,
MAP(
AL,
LAMBDA(
x,
--NOT(
ISNUMBER(
XMATCH(
x,
B
)
)
)
)
),
CF,
SUM(
BF
),
CL,
SUM(
BL
),
L,
CONCAT(
IF(
CL>=3,
TAKE(
FILTER(
AL,
BL
),
3
),
LET(
V,
VSTACK(
IF(
CL>0,
FILTER(
AL,
BL
),
""
),
IF(
ROWS(
AL
)-CL>0,
FILTER(
AL,
NOT(
BL
)
),
""
)
),
W,
IFERROR(
FILTER(
V,
V<>""
),
""
),
IF(
ROWS(
W
)<3,
CONCAT(
W,
REPT(
"X",
3-ROWS(
W
)
)
),
TAKE(
W,
3
)
)
)
)
),
F,
CONCAT(
IF(
CF=3,
FILTER(
AF,
BF
),
IF(
CF>3,
MID(
CONCAT(
FILTER(
AF,
BF
)
),
{1,
3,
4},
1
),
LET(
V,
VSTACK(
IF(
CF>0,
FILTER(
AF,
BF
),
""
),
IF(
ROWS(
AF
)-CF>0,
FILTER(
AF,
NOT(
BF
)
),
""
)
),
W,
IFERROR(
FILTER(
V,
V<>""
),
""
),
IF(
ROWS(
W
)<3,
CONCAT(
W,
REPT(
"X",
3-ROWS(
W
)
)
),
TAKE(
W,
3
)
)
)
)
)
),
TEXTJOIN(
" ",
,
L,
F
)
)
)
)Excel solution 15 for Generate Italian Fiscal Code, proposed by Stefan Olsson:
=MAP(
A2:A11,
LAMBDA(
n,
UPPER(
REGEXREPLACE(
REGEXREPLACE(
REGEXREPLACE(
n,
"(?i)[^ AEIOU]",
""
),
"^(w*)s(w*)$",
REGEXEXTRACT(
REGEXREPLACE(
n,
"(?i)[AEIOU]",
""
),
"s(.*)$"
)&"$2XX "&
REGEXREPLACE(
REGEXREPLACE(
n,
"(?i)[AEIOU]",
""
),
"^(|(w{1,3})|(w)w(ww)w*)s",
"$2$3$4$1XX"
)
),
"^(...).*(s...).*$",
"$1$2"
)
)
)
)Excel solution 16 for Generate Italian Fiscal Code, proposed by Abhishek Kumar Jain:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
x,
v,
{"A",
"E",
"I",
"O",
"U"},
fn,
UPPER(
TEXTBEFORE(
a,
" "
)
),
ln,
UPPER(
TEXTAFTER(
a,
" "
)
),
fnseq,
MID(
fn,
SEQUENCE(
LEN(
fn
)
),
1
),
lnseq,
MID(
ln,
SEQUENCE(
LEN(
ln
)
),
1
),
lncon,
IFERROR(
FILTER(
lnseq,
IFERROR(
XMATCH(
lnseq,
v
),
"CON"
)="CON"
),
""
),
lnvow,
IFERROR(
FILTER(
lnseq,
IFERROR(
XMATCH(
lnseq,
v
),
"CON"
)<>"CON"
),
""
),
fncon,
IFERROR(
FILTER(
fnseq,
IFERROR(
XMATCH(
fnseq,
v
),
"CON"
)="CON"
),
""
),
fnvow,
IFERROR(
FILTER(
fnseq,
IFERROR(
XMATCH(
fnseq,
v
),
"CON"
)<>"CON"
),
""
),
lncode,
IFS(
LEN(
ln
)=1,
ln&"XX",
LEN(
ln
)=2,
TEXTJOIN(
"",
TRUE,
lncon
)&TEXTJOIN(
"",
TRUE,
lnvow
)&"X",
TRUE,
IF(
COUNTA(
lncon
)<3,
LEFT(
TEXTJOIN(
"",
TRUE,
lncon
)&TEXTJOIN(
"",
TRUE,
lnvow
),
3
),
LEFT(
TEXTJOIN(
"",
TRUE,
lncon
),
3
)
)
),
fncode,
IFS(
LEN(
fn
)=1,
fn&"XX",
LEN(
fn
)=2,
TEXTJOIN(
"",
TRUE,
fncon
)&TEXTJOIN(
"",
TRUE,
fnvow
)&"X",
TRUE,
IFS(
COUNTA(
fncon
)<3,
LEFT(
TEXTJOIN(
"",
TRUE,
fncon
)&TEXTJOIN(
"",
TRUE,
fnvow
),
3
),
COUNTA(
fncon
)=3,
LEFT(
TEXTJOIN(
"",
TRUE,
fncon
),
3
),
TRUE,
TEXTJOIN(
"",
TRUE,
INDEX(
fncon,
{1;3;4}
)
)
)
),
lncode &" "&fncode
)
)
)Excel solution 17 for Generate Italian Fiscal Code, proposed by Daniel Garzia:
=MAP(
A2:A11,
LAMBDA(
n,
LET(
a,
UPPER(
n
),
c,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
i,
LAMBDA(
l,
n,
IFERROR(
FILTER(
l,
ISERR(
FIND(
l,
"AEIOU"
)
)-n
),
""
)
),
m,
c(
TEXTAFTER(
a,
" "
)
),
f,
c(
TEXTBEFORE(
a,
" "
)
),
u,
i(
f,
),
LEFT(
CONCAT(
i(
m,
),
i(
m,
1
),
"XX"
),
3
)&" "&LEFT(
CONCAT(
IF(
ROWS(
u
)<4,
u,
INDEX(
u,
{1;3;4}
)
),
i(
f,
1
),
"XX"
),
3
)
)
)
)Excel solution 18 for Generate Italian Fiscal Code, proposed by Diego M.:
=MAP(A2:A11,
LAMBDA(rng,
LET(_N,
TEXTBEFORE(
rng,
" "
),
_SN,
TEXTAFTER(
rng,
" "
),
_Vowels,
{"a",
"e",
"i",
"o",
"u"},
_Spelled_N,
MID(
_N,
SEQUENCE(
LEN(
_N
)
),
1
),
_Spelled_SN,
MID(
_SN,
SEQUENCE(
LEN(
_SN
)
),
1
),
_ix_N_Vowels,
BYROW(--(_Spelled_N=_Vowels),
LAMBDA(
x,
SUM(
x
)
))=1,
_ix_SN_Vowels,
BYROW(--(_Spelled_SN=_Vowels),
LAMBDA(
x,
SUM(
x
)
))=1,
_N_Vowels,
FILTER(
_Spelled_N,
_ix_N_Vowels,
""
),
_N_Cons,
FILTER(
_Spelled_N,
1-_ix_N_Vowels,
""
),
_SN_Vowels,
FILTER(
_Spelled_SN,
_ix_SN_Vowels,
""
),
_SN_Cons,
FILTER(
_Spelled_SN,
1-_ix_SN_Vowels,
""
),
_First,
UPPER(
LEFT(
CONCAT(
VSTACK(
_SN_Cons,
_SN_Vowels,
{"x";"x";"x"}
)
),
3
)
),
_Sec,
UPPER(
LEFT(
CONCAT(
VSTACK(
IF(
& ROWS(
_N_Cons
)>3,
INDEX(
_N_Cons,
{1;3;4}
),
_N_Cons
),
_N_Vowels,
{"x";"x";"x"}
)
),
3
)
),
_First&" "&_Sec)))Excel solution 19 for Generate Italian Fiscal Code, proposed by Diego M.:
= "William Shakespeare"
These two formulas produce the same result (="Shakespeare"):
=TEXTAFTER(
A2,
" "
)
=INDEX(
TEXTSPLIT(
A2,
" "
),
,
2
)
So do the following two (=11) when you apply the LEN function:
=LEN(
TEXTAFTER(
A2,
" "
)
)
=LEN(
INDEX(
TEXTSPLIT(
A2,
" "
),
,
2
)
)
So far so good. Now take the SEQUENCE of the last pair:
=SEQUENCE(
LEN(
TEXTAFTER(
A2,
" "
)
)
)
=SEQUENCE(
LEN(
INDEX(
TEXTSPLIT(
A2,
" "
),
,
2
)
)
)Solving the challenge of Generate Italian Fiscal Code with SQL
SQL solution 1 for Generate Italian Fiscal Code, proposed by Zoran Milokanović:
WITH
DATA_PREPARATION
AS
(
SELECT
F.ORDINAL_NUMBER
,F.NAMES
,CASE
THEN SUBSTRING(F.FNAME_CONSONATS, 1, 1) || SUBSTRING(F.FNAME_CONSONATS, 3)
ELSE F.FNAME_CONSONATS
END AS FNAME_CONSONATS
,F.FNAME_VOWELS
,F.LNAME_CONSONATS
,F.LNAME_VOWELS
FROM
(
SELECT
T.ORDINAL_NUMBER
,T.NAMES
,TRANSLATE(T.FNAME, 'AEIOU', '') AS FNAME_CONSONATS
,TRANSLATE(T.FNAME, '' || TRANSLATE(T.FNAME, 'AEIOU', ''), '') AS FNAME_VOWELS
,TRANSLATE(T.LNAME, 'AEIOU', '') AS LNAME_CONSONATS
,TRANSLATE(T.LNAME, '' || TRANSLATE(T.LNAME, 'AEIOU', ''), '') AS LNAME_VOWELS
FROM
(
SELECT
ROW_NUMBER() OVER () AS ORDINAL_NUMBER
,N.NAMES
,UPPER(SUBSTR(N.NAMES, 1, /*BEFORE SPACE*/ INSTR(N.NAMES, ' ') - 1)) AS FNAME
,UPPER(SUBSTR(N.NAMES, /*AFTER SPACE*/ INSTR(N.NAMES, ' ') + 1)) AS LNAME
FROM NAMES N
) T
) F
)
SELECT
DP.NAMES
,SUBSTRING(SUBSTRING(DP.LNAME_CONSONATS, 1, 3) || SUBSTRING(DP.LNAME_VOWELS, 1, 3) || 'XX', 1, 3) || ' ' ||
SUBSTRING(SUBSTRING(DP.FNAME_CONSONATS, 1, 3) || SUBSTRING(DP.FNAME_VOWELS, 1, 3) || 'XX', 1, 3) AS CODE
FROM DATA_PREPARATION DP
ORDER BY
DP.ORDINAL_NUMBER
;