Convert given strings into Morse codes. 1. This is case insensitive. 2. Mapping table of alphabets and numbers is on right side. 3. Mars code for space will be “/” 4. There will always be a space between two morse codes. Hence ab will be “.- -…” not “.–…”. Notice a space between “.-” and “-…”
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 125
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert Strings to Morse with Power Query
Power Query solution 1 for Convert Strings to Morse, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MT = Table.ToColumns(Excel.CurrentWorkbook(){[Name = "MORSECODE"]}[Content]),
MC = Table.Buffer(
Table.FromColumns(
{
List.Transform(List.Combine(List.Alternate(MT, 1, 1, 1)) & {" "}, Text.From),
List.Combine(List.Alternate(MT, 1, 1, 0)) & {"/"}
},
{"C", "M"}
)
),
Code = Table.TransformRows(
Source,
each Text.Combine(
(
List.Transform(
Text.ToList(Text.Upper([String])),
(l) => Table.SelectRows(MC, each [C] = l)[M]{0}
)
),
" "
)
)
in
CodePower Query solution 2 for Convert Strings to Morse, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MorseCode = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
MorseZipped = List.Zip(
{
{"A" .. "Z"} & {"0" .. "9"} & {" "},
List.Combine(
List.Alternate(List.Transform(Table.ToColumns(MorseCode), each List.RemoveNulls(_)), 1, 1)
)
& {"/"}
}
),
Split = Table.AddColumn(Source, "Custom", each Text.ToList([String])),
Sol = Table.AddColumn(
Split,
"Result",
each Text.Combine(
List.Transform(
List.ReplaceMatchingItems([Custom], MorseZipped, Comparer.OrdinalIgnoreCase),
Text.From
),
" "
)
)[[Result]]
in
SolPower Query solution 3 for Convert Strings to Morse, proposed by Luan Rodrigues:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
Lista =
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela2"]}[Content],
tipo = Table.TransformColumnTypes(Fonte, {{"Coluna5", type text}}),
result = List.Distinct(
List.Zip(List.Split(Table.ToColumns(tipo), 2){0})
& List.Zip(List.Split(Table.ToColumns(tipo), 2){1})
& List.Zip(List.Split(Table.ToColumns(tipo), 2){2})
)
in
result,
sub = Table.TransformColumns(
Fonte,
{
{
"String",
each Text.Combine(
List.ReplaceMatchingItems(Text.ToList(_), Lista, Comparer.OrdinalIgnoreCase),
" "
),
type text
}
}
),
res = Table.ReplaceValue(sub, " ", " / ", Replacer.ReplaceText, {"String"})
in
resPower Query solution 4 for Convert Strings to Morse, proposed by Rafael González B.:
let
LettersTable=
let
Source0 = Excel.CurrentWorkbook(){[Name="StringTexts"]}[Content],
GetTables = Table.AddColumn(Source0, "Letters", each Table.AddIndexColumn(
Table.FromList( Text.ToList( Text.Lower( Text.Replace([String], " ","/"))),null,{"Letters"}),"Index",1))
in
GetTables[[Letters]],
CodeTable = let
Source1 = Excel.CurrentWorkbook(){[Name="Codes"]}[Content],
TransformText = Table.TransformColumns(Source1, {"Letters", each Text.Lower(Text.From(_))})
in
TransformText,
Result = Table.TransformColumns(LettersTable,
{"Letters", each Text.Combine(Table.ReplaceValue(
Table.Sort(
Table.ExpandTableColumn(
Table.RemoveColumns(
Table.NestedJoin(_, {"Letters"},
CodeTable, {"Letters"},"NestedCode"),
Letters"), "NestedCode", {"Code"}), "Index"),
null,"/",Replacer.ReplaceValue, {"Code"})[Code],
" ")}
)
in
Result
Power Query solution 5 for Convert Strings to Morse, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
CharacterRecs = Table.ToRecords(Table.SelectRows(Table.TransformColumnTypes(Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),2), each Table.FromColumns(_, {"From", "To"}))),{{"From", type text}}), each ([From] <> null))) & Table.ToRecords(hashtag#table({"From","To"},{{" "," /"}})),
GetStrings = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn(GetStrings, "Result", each List.Accumulate( CharacterRecs, Text.Combine(Text.ToList(Text.Upper([String]))," "), (s,c)=> Text.Replace(s, c[From], c[To] ) ) )
in
Result
Solving the challenge of Convert Strings to Morse with Excel
Excel solution 1 for Convert Strings to Morse, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
c,
TOCOL(
D2:I14,
3
)&"",
TEXTJOIN(
" ",
,
IFNA(
INDEX(
c,
1+XMATCH(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
)
),
"/"
)
)
)
)
)Excel solution 2 for Convert Strings to Morse, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A8,
LAMBDA(
a,
TEXTJOIN(
" ",
,
IFNA(
VLOOKUP(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
WRAPROWS(
TOCOL(
D2:I14
),
2
)&"",
2,
),
"/"
)
)
)
)Excel solution 3 for Convert Strings to Morse, proposed by Rick Rothstein:
=LET(
a,
D2:G14,
t,
VSTACK(
TAKE(
a,
,
2
),
TAKE(
a,
,
-2
),
""&H2:I11,
{" ",
"/"}
),
MAP(
A2:A8,
LAMBDA(
x,
TEXTJOIN(
" ",
,
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
INDEX(
t,
,
1
),
INDEX(
t,
,
2
)
)
)
)
)
)Excel solution 4 for Convert Strings to Morse, proposed by John V.:
=MAP(
A2:A8,
LAMBDA(
s,
TEXTJOIN(
" ",
,
MAP(
MID(
s,
ROW(
1:20
),
1
),
LAMBDA(
x,
CONCAT(
IF(
x=" ",
"/",
REPT(
E2:I14,
D2:H14&""=x
)
)
)
)
)
)
)
)Excel solution 5 for Convert Strings to Morse, proposed by John V.:
=MAP(
A2:A8,
LAMBDA(
x,
TEXTJOIN(
" ",
,
IFNA(
VLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
WRAPROWS(
TOCOL(
D2:I14&"",
2
),
2
),
2,
),
"/"
)
)
)
)Excel solution 6 for Convert Strings to Morse, proposed by محمد حلمي:
=MAP(
A2:A8,
LAMBDA(
v,
TRIM(
CONCAT(
SCAN(
"",
XLOOKUP(
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
VSTACK(
D2:D14,
F2:F14,
H2:H14,
CHAR(
32
)
)&"",
VSTACK(
E2:E14,
G2:G14,
I2:I14,
"/"
)
),
LAMBDA(
a,
d,
d&" "
)
)&" "
)
)
)
)Excel solution 7 for Convert Strings to Morse, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A8,
LAMBDA(
x,
TEXTJOIN(
" ",
,
IFNA(
LOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
SORT(
WRAPROWS(
TOCOL(
D2:I14,
1
)&"",
2
)
)
),
"/"
)
)
)
)Excel solution 8 for Convert Strings to Morse, proposed by 🇰🇷 Taeyong Shin:
=LET(
tbl,
VSTACK(
WRAPROWS(
TOCOL(
D2:I14 & "",
1
),
2
),
{" ",
"/"}
),
str,
VLOOKUP(
MID(
A2:A8,
SEQUENCE(
,
MAX(
LEN(
A2:A8
)
)
),
1
),
tbl,
2,
0
),
BYROW(
str,
LAMBDA(
br,
TEXTJOIN(
" ",
,
br
)
)
)
)Excel solution 9 for Convert Strings to Morse, proposed by Kris Jaganah:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
b,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
c,
XLOOKUP(
b,
VSTACK(
D2:D14,
F2:F14,
TEXT(
H2:H11,
"0"
),
" "
),
VSTACK(
E2:E14,
G2:G14,
I2:I11,
"/"
)
),
TEXTJOIN(
" ",
TRUE,
c
)
)
)
)Excel solution 10 for Convert Strings to Morse, proposed by Julian Poeltl:
=MAP(
A2:A8,
LAMBDA(
A,
LET(
T,
WRAPROWS(
TOCOL(
D2:I14,
1
),
2
),
C,
TAKE(
T,
,
1
),
M,
TAKE(
T,
,
-1
),
SP,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
TEXTJOIN(
" ",
,
XLOOKUP(
IFERROR(
SP*1,
SP
),
VSTACK(
C,
" "
),
VSTACK(
M,
"/"
)
)
)
)
)
)Excel solution 11 for Convert Strings to Morse, proposed by Timothée BLIOT:
=LET(
A,
A2:A8,
L,
TEXT(
VSTACK(
D2:D14,
F2:F14,
H2:H11,
{" "}
),
"0"
),
M,
VSTACK(
E2:E14,
G2:G14,
I2:I11,
{"/"}
),
B,
TEXTSPLIT(
TEXTJOIN(
"/",
,
MAP(
A,
LAMBDA(
a,
TEXTJOIN(
":",
,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)
)
),
":",
"/",
,
,
""
),
C,
MAP(
B,
LAMBDA(
b,
XLOOKUP(
b,
L,
M,
""
)&" "
)
),
TRIM(
BYROW(
C,
LAMBDA(
c,
CONCAT(
c
)
)
)
)
)Excel solution 12 for Convert Strings to Morse, proposed by Hussein SATOUR:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
VSTACK(
D2:E14,
F2.G14,
TEXT(
H2.I11,
"@"
),
{" ",
"/"}
),
TEXTJOIN(
" ",
,
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
INDEX(
a,
,
1
),
INDEX(
a,
,
2
)
)
)
)
)
)Excel solution 13 for Convert Strings to Morse, proposed by Sunny Baggu:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
_U,
UPPER(
SUBSTITUTE(
a,
" ",
"/"
)
),
_SP,
MID(
_U,
SEQUENCE(
LEN(
_U
)
),
1
),
_tbl,
SORT(
WRAPROWS(
TOCOL(
D2:I14,
3
),
2
)
),
TRIM(
CONCAT(
XLOOKUP(
_SP,
VALUETOTEXT(
CHOOSECOLS(
_tbl,
1
)
),
CHOOSECOLS(
_tbl,
2
),
"/",
0
)&" "
)
)
)
)
)Excel solution 14 for Convert Strings to Morse, proposed by Md. Zohurul Islam:
=LET(
p,
A2:A8,
q,
D2:I14,
u,
VSTACK(
TRIM(
WRAPROWS(
TOCOL(
q,
1
),
2
)
),
{" ",
"/"}
),
v,
REDUCE(
"Formula Solution",
p,
LAMBDA(
x,
y,
LET(
a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
MAP(
a,
LAMBDA(
p,
VLOOKUP(
p,
u,
2,
0
)
)
),
d,
TEXTJOIN(
" ",
1,
b
),
e,
VSTACK(
x,
d
),
e
)
)
),
v
)Excel solution 15 for Convert Strings to Morse, proposed by Charles Roldan:
=MAP(
A2:A8,
LAMBDA(
x,
TRIM(
REDUCE(
UPPER(
x
),
BYROW(
WRAPROWS(
TOROW(
D2:I14,
1
),
2
),
LAMBDA(
y,
TEXTJOIN(
";",
,
y
)
)
)&" ",
LAMBDA(
a,
b,
SUBSTITUTE(
a,
TEXTBEFORE(
b,
";"
),
TEXTAFTER(
b,
";"
)
)
)
)
)
)
)Excel solution 16 for Convert Strings to Morse, proposed by Jaroslaw Kujawa:
=BYROW(
A2:A8,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
TEXTJOIN(
";",
,
Morse
),
,
";"
),
c,
IFNA(
1+XMATCH(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
b
),
),
TEXTJOIN(
" ",
,
IF(
c,
INDEX(
b,
c
),
"/"
)
)
)
)
)Excel solution 17 for Convert Strings to Morse, proposed by Stefan Olsson:
=BYROW(
A2:A8,
LAMBDA(
br,
TEXTJOIN(
" ",
TRUE,
MAP(
SEQUENCE(
1,
LEN(
br
),
1,
1
),
LAMBDA(
x,
XLOOKUP(
MID(
br,
x,
1
),
{D2:D14; F2:F14; H2:H11},
{E2:E14; G2:G14; I2:I11},
"/"
)
)
)
)
)
)Excel solution 18 for Convert Strings to Morse, proposed by Abhishek Kumar Jain:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
UPPER(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
b,
VALUETOTEXT(
WRAPROWS(
TOCOL(
D2:I14,
1
),
2
)
),
TRIM(
TEXTJOIN(
" ",
TRUE,
XLOOKUP(
a,
INDEX(
b,
,
1
),
INDEX(
b,
,
2
),
"/"
)
)
)
)
)
)Excel solution 19 for Convert Strings to Morse, proposed by Guillermo Arroyo:
=MID(
LET(
_p,
SORT(
VSTACK(
{" ",
"/"},
TEXT(
WRAPROWS(
TOCOL(
D2:I14,
3,
0
),
2
),
0
)
),
1,
1,
0
),
_f,
LAMBDA(
_a,
_b,
_c,
IF(
_b="",
_c,
_a(
_a,
MID(
_b,
2,
99
),
_c&" "&XLOOKUP(
UPPER(
LEFT(
_b,
1
)
),
INDEX(
_p,
0,
1
),
INDEX(
_p,
0,
2
),
"B",
0,
1
)
)
)
),
MAP(
A2:A8,
LAMBDA(
_m,
_f(
_f,
_m,
""
)
)
)
),
2,
999
)Excel solution 20 for Convert Strings to Morse, proposed by Rayan S.:
=LET(
code,
VSTACK(
D2:E14,
F2:G14,
H2:I11
),
MAP(
A2:A8,
LAMBDA(
a,
TEXTJOIN(
" ",
,
IFERROR(
INDEX(
TAKE(
code,
,
-1
),
IFERROR(
XMATCH(
TOROW(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
TAKE(
code,
,
1
),
0
),
XMATCH(
TOROW(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)+0,
TAKE(
code,
,
1
),
0
)
),
1
),
"/"
)
)
)
)
)Excel solution 21 for Convert Strings to Morse, proposed by Diego M.:
=LET(
lkp,
""&VSTACK(
D2:D14,
F2:F14,
H2:H11,
" "
),
rtn,
VSTACK(
E2:E14,
G2:G14,
I2:I11,
"/"
),
MAP(
A2:A8,
LAMBDA(
x,
LET(
spell,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
TEXTJOIN(
" ",
,
XLOOKUP(
spell,
lkp,
rtn
)
)
)
)
)
)Solving the challenge of Convert Strings to Morse with SQL
_x000D_SQL solution 1 for Convert Strings to Morse, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP_INPUT
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDERING
,ID.STRING
FROM INPUT_DATA ID
),
DATA_PREP_MORSE
AS
(
SELECT
MCT.D AS LETTER
,MCT.E AS CODE
FROM MORSE_CODE_TABLE MCT
UNION ALL
SELECT
MCT.F
,MCT.G
FROM MORSE_CODE_TABLE MCT
UNION ALL
SELECT
MCT.H
,MCT.I
FROM MORSE_CODE_TABLE MCT
WHERE
MCT.I <> ''
),
CALC
AS
(
SELECT
I.ORDERING
,I.STRING
,SUBSTRING(I.STRING, 1, 1) AS LETTER
,SUBSTRING(I.STRING, 2, LEN(I.STRING) - 1) AS REMAINDER
FROM DATA_PREP_INPUT I
UNION ALL
SELECT
C.ORDERING
,C.STRING
,SUBSTRING(C.REMAINDER, 1, 1) AS LETTER
,SUBSTRING(C.REMAINDER, 2, LEN(C.STRING) - 1) AS REMAINDER
FROM CALC C
WHERE
C.REMAINDER <> ''
)
SELECT
C.STRING
,C.RESULT
FROM CALC C
WHERE
C.REMAINDER = ''
ORDER BY
C.ORDERING
;