Fill in the missing letters between two letters in the given words. 1. Letters should be in sequence (increasing or decreasing) exclusive of two letters. ex. ad = abcd, da = dcba 2. If both letters are same, nothing needs to be filled in. ex. ee = ee Ex. jeep = j ihgf e e fghijklmno p Note – I have given space just for the clarity. Answers will not carry space.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 568
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Expand Alphabet Letter Gaps with Power Query
Power Query solution 1 for Expand Alphabet Letter Gaps, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
TL = Text.ToList([Words]),
L = List.Count(TL) - 2,
S = List.Transform(
{0 .. L},
(f) =>
[
s = TL{f},
e = TL{f + 1},
l1 = {s .. e},
c = List.IsEmpty(l1),
l2 = List.Reverse({e .. s}),
r = List.Skip(if s = e then {1, s} else if c then l2 else l1)
][r]
),
C = Text.Combine(List.Combine(S)),
R = TL{0} & C
][R]
)
in
ReturnPower Query solution 2 for Expand Alphabet Letter Gaps, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Custom",
each
let
a = Text.ToList([Words]),
b = List.Transform(
{0 .. List.Count(a) - 2},
each
let
b1 = {a{_} .. a{_ + 1}},
b2 = if List.IsEmpty(b1) then List.Reverse({a{_ + 1} .. a{_}}) else b1,
b3 = if List.Count(b2) = 1 then b2 else List.RemoveLastN(b2)
in
b3
)
in
Text.Combine(List.Combine(b)) & Text.End([Words], 1)
)
in
SolPower Query solution 3 for Expand Alphabet Letter Gaps, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FillMissingLetters = (word) =>
[
a = Text.ToList(word),
b = Text.Combine({"a" .. "z"}),
c = List.Accumulate(
{0 .. Text.Length(word) - 2},
"",
(x, y) =>
[
p = Text.PositionOf(b, a{y}),
q = Text.PositionOf(b, a{y + 1}),
r = try Text.Middle(b, p + 1, q - p - 1) otherwise "",
s = try Text.Reverse(Text.Middle(b, q + 1, p - q - 1)) otherwise "",
t = x & a{y} & r & s
][t]
),
d = c & Text.End(word, 1)
][d],
AddColumn = Table.AddColumn(Source, "My Answer", each FillMissingLetters([Words])),
Result = Table.AddColumn(AddColumn, "Check", each [Answer Expected] = [My Answer])
in
ResultPower Query solution 4 for Expand Alphabet Letter Gaps, proposed by Tyler N.:
let
a = YourTable,
b = Table.AddColumn(
a,
"A",
each
let
c = Text.ToList([Words]),
d = List.Transform(
{0 .. List.Count(c) - 2},
each
let
e = _,
f = List.Transform({0, 1}, each {c{e + _} .. c{e + (1 - _)}}),
g = Text.Combine(List.Skip(List.Reverse(f{1}) & f{0}, 1))
in
g
)
in
c{0} & Text.Combine(d)
)
in
bSolving the challenge of Expand Alphabet Letter Gaps with Excel
Excel solution 1 for Expand Alphabet Letter Gaps, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
BYROW(
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
)+{0,
1},
1
)
)*{1,
-1},
LAMBDA(
c,
CONCAT(
CHAR(
IFERROR(
SEQUENCE(
ABS(
SUM(
c
)
),
,
@c,
SIGN(
-SUM(
c
)
)
),
@c
)
)
)
)
)
)
)
)Excel solution 2 for Expand Alphabet Letter Gaps, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
w,
LET(
s,
SEQUENCE,
c,
CONCAT,
l,
LEFT,
d,
FIND,
m,
MID,
f,
c(
CHAR(
s(
26,
,
97
)
)
),
r,
c(
CHAR(
s(
26,
,
122,
-1
)
)
),
REDUCE(
RIGHT(
w
),
m(
w,
s(
LEN(
w
)-1,
,
LEN(
w
)-1,
-1
),
1
),
LAMBDA(
a,
x,
IF(
x=l(
a
),
x,
IF(
xExcel solution 3 for Expand Alphabet Letter Gaps, proposed by John V.:
=MAP(A2:A10,LAMBDA(w,CONCAT(MAP(SEQUENCE(LEN(w)-1),LAMBDA(x,LET(c,CONCAT,a,MID(w,x,1),b,MID(w,1+x,1),i,c(CHAR(SORT(ROW(97:122),,-1^(a>b)))),p,FIND(a,i),c(MID(i,p,MAX(1,FIND(b,i)-p)))))),RIGHT(w))))
And other option, using Bo Rydobon 🇹🇭's brilliant idea :
✅
=MAP(A2:A10,LAMBDA(w,CONCAT(BYROW(CODE(MID(w,SEQUENCE(LEN(w)-1)+{0,1},1))*{1,-1},LAMBDA(x,CONCAT(CHAR(SEQUENCE(MAX(1,ABS(SUM(x))),,@x,-1^(SUM(x)>0)))))),RIGHT(w))))Excel solution 4 for Expand Alphabet Letter Gaps, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(z,LET(c,MID(z,SEQUENCE(LEN(z)),1),e,SEQUENCE(26,,97),CONCAT(IFNA(MAP(CODE(c),CODE(DROP(c,1)),LAMBDA(x,y,IFERROR(CONCAT(FILTER(CHAR(e),(e>=x)*(ey)*(e<=x),x))))))),TAKE(c,-1)))))) Excel solution 5 for Expand Alphabet Letter Gaps, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
W,
LET(
L,
LEN(
W
),
S,
MID(
W,
SEQUENCE(
L
),
1
),
C,
CODE(
S
),
D,
DROP(
DROP(
C,
1
)-C,
-1
),
CONCAT(
IFERROR(
MAP(
D,
DROP(
C,
-1
),
LAMBDA(
A,
S,
CONCAT(
CHAR(
SEQUENCE(
ABS(
A
),
,
S,
IF(
A<0,
-1,
1
)
)
)
)
)
),
IF(
L<3,
"",
S
)
)
)&IF(
L=2,
RIGHT(
W
),
""
)
)
)
)Excel solution 6 for Expand Alphabet Letter Gaps, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
seq,
SEQUENCE(
LEN(
a
) - 1
),
pr,
MID(
a,
seq,
1
),
nx,
MID(
a,
seq + 1,
1
),
char1,
CONCAT(
CHAR(
SEQUENCE(
26,
,
97
)
)
),
char2,
CONCAT(
CHAR(
SEQUENCE(
26,
,
122,
-1
)
)
),
fnd1,
FIND(
pr,
char1
),
fnd2,
FIND(
nx,
char1
),
abs,
ABS(
fnd2 - fnd1
),
mid,
IFS(
fnd2 > fnd1,
MID(
char1,
fnd1 + 1,
abs
),
fnd2 < fnd1,
MID(
char2,
26 - fnd1 + 2,
abs
),
1,
pr
),
rtrn,
LEFT(
a
) & CONCAT(
mid
),
rtrn
)
)
)Excel solution 7 for Expand Alphabet Letter Gaps, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
y,
LET(
M,
MID,
S,
SEQUENCE,
C,
CONCAT,
a,
C(
CHAR(
S(
26
)+96
)
),
z,
C(
CHAR(
S(
26,
,
26,
-1
)+96
)
),
C(
BYROW(
M(
y,
S(
LEN(
y
)-1
),
2
),
LAMBDA(
x,
LET(
l,
FIND(
LEFT(
x
),
a
),
r,
FIND(
RIGHT(
x
),
a
),
IFS(
lr,
M(
z,
26-l+1,
l-r
),
1,
LEFT(
x
)
)
)
)
),
RIGHT(
y
)
)
)
)
) Excel solution 8 for Expand Alphabet Letter Gaps, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(x,
LET(
_s,
SEQUENCE(
26
),
_sc,
CHAR(
_s + CODE(
"a"
) - 1
),
_rs,
SORT(
_s,
,
-1
),
_rsc,
XLOOKUP(
_rs,
_s,
_sc
),
_m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
_n,
XLOOKUP(
_m,
_sc,
_s
),
_p,
DROP(
_n,
-1
),
_q,
DROP(
_n,
1
),
_t,
MAP(
_p,
_q,
LAMBDA(a,
b,
CONCAT(IF(a > b,
FILTER(_rsc,
(_rs <= a) * (_rs >= b)),
FILTER(_sc,
(_s >= a) * (_s <= b)))))
),
_g,
RIGHT(
_t,
LEN(
_t
) - 1
),
_r,
IF(
LEN(
_g
) = 0,
_t,
_g
),
IFS(
LEN(
x
) = 2,
TAKE(
_t,
1
),
LEN(
x
) = 3,
CONCAT&(
VSTACK(
TAKE(
_t,
1
),
TAKE(
_r,
-1
)
)
),
1,
CONCAT(
VSTACK(
TAKE(
_t,
1
),
DROP(
DROP(
_r,
1
),
-1
),
TAKE(
_r,
-1
)
)
)
)
)
)
)Excel solution 9 for Expand Alphabet Letter Gaps, proposed by Bilal Mahmoud kh.:
=MAP(
A2:A10,
LAMBDA(
N,
CONCAT(
MAP(
MID(
N,
SEQUENCE(
LEN(
N
)-1
),
1
),
MID(
N,
SEQUENCE(
LEN(
N
)-1,
,
2
),
1
),
LAMBDA(
x,
y,
IF(
CODE(
x
)=CODE(
y
),
x,
IF(
CODE(
x
)Excel solution 10 for Expand Alphabet Letter Gaps, proposed by Philippe Brillault:
=BYROW(A2:A10, LAMBDA(e, FILLR(e)))Solving the challenge of Expand Alphabet Letter Gaps with Python
Python solution 1 for Expand Alphabet Letter Gaps, proposed by Konrad Gryczan, PhD:
import pandas as pd
from itertools import chain
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)
def fill_words(string):
result = []
def fill_sequence(first, second):
return [chr(n) for n in range(ord(first), ord(second))]
return [chr(n) for n in range(ord(first), ord(second), -1)]
return [first]
result.extend(chain.from_iterable(fill_sequence(string[i], string[i + 1]) for i in range(len(string) - 1)))
result.append(string[-1])
return "".join(result)
input['Answer Expected'] = input.iloc[:, 0].apply(fill_words)
result = input.drop(columns=input.columns[0])
print(result.equals(test)) # True
Solving the challenge of Expand Alphabet Letter Gaps with Python in Excel
Python in Excel solution 1 for Expand Alphabet Letter Gaps, proposed by Alejandro Campos:
def fill_missing_letters(word):
result = []
for i in range(len(word) - 1):
start, end = word[i], word[i + 1]
if start < end:
result.append(start + ''.join(chr(c) for c in range(ord(start) + 1, ord(end))))
elif start > end:
result.append(start + ''.join(chr(c) for c in range(ord(start) - 1, ord(end), -1)))
else:
result.append(start)
result.append(word[-1])
return ''.join(result)
df = pd.DataFrame(words, columns=["Words"])
df["Filled"] = df["Words"].apply(fill_missing_letters)
df
Python in Excel solution 2 for Expand Alphabet Letter Gaps, proposed by Ümit Barış Köse, MSc:
def fill_missing_letters(word):
for i in range(len(word) - 1):
start, end = ord(word[i]), ord(word[i+1])
step = 1 if start < end else -1
filled_word.extend([chr(c) for c in range(start + step, end, step)])
filled_word.append(word[i+1])
return ''.join(filled_word)
df = pd.DataFrame(results, columns=['Words', 'My Answer'])
Solving the challenge of Expand Alphabet Letter Gaps with R
R solution 1 for Expand Alphabet Letter Gaps, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
lets = strsplit(string, "")[[1]]
pairs = map(1:(length(lets) - 1), ~paste(lets[.x:(.x + 1)], collapse = ""))
df = tibble(
)
df = df %>%
mutate(
first_num = as.numeric(charToRaw(.x))
third_num = as.numeric(charToRaw(.y))
letters = map_chr((first_num):(third_num), ~rawToChar(as.raw(.x)))
paste(letters, collapse = "") %>% str_sub(2, -2)
}),
) %>%
unite("word", c("first", "second", "third"), sep = "") %>%
pull(word) %>%
paste(collapse = "")
return(df)
}
fill_words("planet")
result = input %>%
mutate(`Answer Expected` = map_chr(Words, fill_words)) %>%
select(-Words)
all.equal(result$`Answer Expected`, test$`Answer Expected`, check.attributes = FALSE)
#> [1] TRUE
&&