Arrange the given characters in such a fashion that alphabets occupy all odd positions and numbers occupy even position in the order in which they appear. If alphabets or numbers run out, then no need to follow the rule. Ex. ma5n6 => m5a6n 83ex4cel => e8x3c4el
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 366
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Alternate Letters and Numbers with Power Query
Power Query solution 1 for Alternate Letters and Numbers, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
C = Text.Combine,
R = Table.AddColumn(S, "R", each
let
f = (l) => List.Select(Text.ToList([Words]), each List.ContainsAny({_}, l)),
a = C(List.Transform(List.Zip({f({"a".."z"}), f({"0".."9"})}), each C(_)))
in
a
)[[R]]
in
R
Blessings!
Power Query solution 2 for Alternate Letters and Numbers, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Answer Expected",
each
let
a = Text.ToList([Words]),
b = List.RemoveNulls(List.Transform(a, each try Text.From(Number.From(_)) otherwise null)),
c = Text.Combine(
List.Combine(
List.Combine(List.Transform(List.Zip({List.RemoveItems(a, b), b}), each {_}))
)
)
in
c
)
in
AnsPower Query solution 3 for Alternate Letters and Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
L = List.Transform({{"a" .. "z"}, {"0" .. "9"}}, (f) => Text.ToList(Text.Select([Words], f))),
Z = List.Zip(L),
R = Text.Combine(List.Combine(Z))
][R]
)
in
ReturnPower Query solution 4 for Alternate Letters and Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Transform(
{{"a" .. "z"}, {"0" .. "9"}},
(x) => Text.ToList(Text.Select([Words], x))
),
b = Text.Combine(List.Combine(List.Zip({a{0}, a{1}})))
in
b
)[[Answer]]
in
SolPower Query solution 5 for Alternate Letters and Numbers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each Text.Combine(
List.Transform(
{[Words]},
(x) =>
[
t = Text.ToList(Text.Select(x, {"a" .. "z"})),
n = Text.ToList(Text.Select(x, {"0" .. "9"})),
c = List.Combine(List.Zip({t, n}))
][c]
){0}
)
)
in
resPower Query solution 6 for Alternate Letters and Numbers, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x) =>
let
a = Text.ToList(x),
b = List.RemoveNulls(
Table.ReplaceErrorValues(
Table.FromColumns({List.Transform(a, Number.From)}, {"C1"}),
{{"C1", null}}
)[C1]
),
c = List.Transform(b, Text.From),
d = List.Positions(c),
e = List.Difference(a, c),
f = List.Positions(e),
g = Table.FromColumns({e & c, f & d}, {"C1", "C2"}),
h = Text.Combine(Table.Sort(g, {{"C2", 0}, {"C1", 1}})[C1])
in
h,
Sol = Table.AddColumn(Origen, "Answer Expected", each Fx([Words]))
in
SolPower Query solution 7 for Alternate Letters and Numbers, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Words", type text}, {"Answer Expected", type text}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each [
L = Text.ToList([Words]),
LT = List.Zip({L, List.Positions(L)}),
N = List.Select(LT, (x) => List.Contains({"0" .. "9"}, x{0})),
T = List.Select(LT, (x) => List.Contains({"A" .. "Z", "a" .. "z"}, x{0})),
NL = List.Count(N),
TL = List.Count(T),
NorT = if NL < TL then NL else if TL < NL then TL else NL,
RL = List.Zip({List.FirstN(T, NorT), List.FirstN(N, NorT)}),
RA = List.Accumulate(
L,
[n = 0, t = 0, r1 = {}, r2 = {}],
(S, C) =>
if List.Contains({"0" .. "9"}, C) then
[n = S[n] + 1, t = S[t], r1 = S[r1] & {C}, r2 = S[r2] & {n}]
else
[n = S[n], t = S[t] + 1, r1 = S[r1] & {C}, r2 = S[r2] & {t}]
),
RT = Table.SelectRows(Table.FromColumns({RA[r1], RA[r2]}, {"r1", "r2"}), (x) => x[r2] > NorT)[
r1
],
R = List.Accumulate(RL, "", (S, C) => S & C{0}{0} & C{1}{0}) & Text.Combine(RT)
][R]
)
in
#"Added Custom"Solving the challenge of Alternate Letters and Numbers with Excel
Excel solution 1 for Alternate Letters and Numbers, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(50),m,MID(a,s,1),y,m<"a",b,SUM(1-y),CONCAT(SORTBY(SORTBY(m,y),s-b*(s>b))))))Excel solution 2 for Alternate Letters and Numbers, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
f,
LAMBDA(
x,
y,
CONCAT(
TEXTSPLIT(
a,
CHAR(
SEQUENCE(
x,
,
y
)
)
)
)
),
t,
f(
10,
48
),
n,
f(
26,
97
),
g,
LAMBDA(
z,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
)
),
IFERROR(
CONCAT(
TOROW(
IFERROR(
HSTACK(
g(
t
),
g(
n
)
),
1/0
),
3
)
),
a
)
)
)
)Excel solution 3 for Alternate Letters and Numbers, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
c,
MID(
x,
ROW(
1:20
),
1
),
CONCAT(
IFNA(
HSTACK(
FILTER(
c,
c>"9"
),
FILTER(
c,
c<"a"
)
),
""
)
)
)
)
)Excel solution 4 for Alternate Letters and Numbers, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
MID(
a,
SEQUENCE(
30
),
1
),
e,
FILTER(
d,
d<"a"
),
CONCAT(
IFNA(
FILTER(
d,
d>"9"
)&e,
e
)
)
)
)
)
For understand <"a" & >"9",
Please test this
=SORT(
CHAR(
SEQUENCE(
255
)
)
)Excel solution 5 for Alternate Letters and Numbers, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
TOCOL(
HSTACK(
FILTER(
a,
ISERR(
-a
)
),
FILTER(
a,
ISNUMBER(
-a
),
""
)
),
3
)
)
)
)
)Excel solution 6 for Alternate Letters and Numbers, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
W,
LET(
L,
LEN(
W
),
SP,
MID(
W,
SEQUENCE(
1,
L
),
1
),
IN,
ISNUMBER(
SP*1
),
CW,
L-SUM(
--IN
),
S,
SORTBY(
SP,
IN,
1
),
M,
MIN(
CW,
L-CW
),
AL,
CHOOSECOLS(
S,
SEQUENCE(
1,
CW
)
),
AN,
COOSECOLS(
S,
SEQUENCE(
1,
L-CW,
CW+1
)
),
IF(
M>0,
CONCAT(
BYCOL(
IFERROR(
VSTACK(
AL,
AN
),
""
),
LAMBDA(
A,
CONCAT(
A
)
)
)
),
W
)
)
)
)Excel solution 7 for Alternate Letters and Numbers, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
x,
CONCAT(
TOCOL(
VSTACK(
IFNA(
REGEXEXTRACT(
x,
"[a-z]",
1
),
""
),
IFNA(
REGEXEXTRACT(
x,
"d",
1
),
""
)
),
3,
1
)
)
)
)Excel solution 8 for Alternate Letters and Numbers, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
x,
IFERROR(
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
IFNA(
HSTACK(
FILTER(
a,
ISERR(
--a
)
),
FILTER(
a,
ISNUMBER(
--a
)
)
),
""
)
)
),
x
)
)
)Excel solution 9 for Alternate Letters and Numbers, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
_a,
FILTER(
m,
m > "9"
),
_b,
FILTER(
m,
m < "a"
),
IFERROR(
CONCAT(
IFNA(
HSTACK(
_a,
_b
),
""
)
),
x
)
)
)
)Excel solution 10 for Alternate Letters and Numbers, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(A2:A10,LAMBDA(l,LET(x,LEN(l),a,MID(l,SEQUENCE(x),1),f,LAMBDA(v,EXPAND(FILTER(a,IF(v,a>"9",a<"a"),""),x,,"")),CONCAT(f(1)&f(0)))))Excel solution 11 for Alternate Letters and Numbers, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A10,
LAMBDA(
l,
LET(
x,
LEN(
l
),
a,
MID(
l,
SEQUENCE(
x
),
1
),
CONCAT(
EXPAND(
FILTER(
a,
a>"9",
""
),
x,
,
""
) & EXPAND(
FILTER(
a,
a<"a",
""
),
x,
,
""
)
)
)
)
)Excel solution 12 for Alternate Letters and Numbers, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A10,
LAMBDA(
l,
LET(
x,
LEN(
l
),
a,
MID(
l,
SEQUENCE(
x
),
1
),
b,
IFERROR(
a*1,
a
),
CONCAT(
EXPAND(
FILTER(
b,
b>"",
""
),
x,
,
""
) & EXPAND(
FILTER(
b,
b<"",
""
),
x,
,
""
)
)
)
)
)Excel solution 13 for Alternate Letters and Numbers, proposed by Charles Roldan:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
ISNUMBER(
--a
),
f,
LAMBDA(
t,
FILTER(
a,
t,
""
)
),
CONCAT(
TOCOL(
HSTACK(
f(
1-b
),
f(
b
)
),
2
)
)
)
)
)Excel solution 14 for Alternate Letters and Numbers, proposed by Pieter de Bruijn:
=LET(
s,
SEQUENCE(
26
),
MAP(
A2:A10,
LAMBDA(
x,
LET(
z,
LAMBDA(
b,
MID(
CONCAT(
TEXTSPLIT(
x,
b
)
),
s,
1
)
),
CONCAT(
z(
SEQUENCE(
,
10,
0
)
)&z(
CHAR(
96+s
)
)
)
)
)
)
)Excel solution 15 for Alternate Letters and Numbers, proposed by Giorgi Goderdzishvili:
=MAP(A2:A10,LAMBDA(t,LET(
_wr,t,
_cr,MID(_wr,SEQUENCE(,LEN(_wr)),1),
_nm,FILTER(_cr,ISNUMBER(--_cr)),
_tx,FILTER(_cr,NOT(ISNUMBER(--_cr))),
_ch,IF(COLUMNS(_nm)>=COLUMNS(_tx),EXPAND(_tx,,COLUMNS(_nm),"")&_nm,_tx&EXPAND(_nm,,COLUMNS(_tx),"")),
IFERROR(CONCAT(_ch),_wr))))Excel solution 16 for Alternate Letters and Numbers, proposed by Edwin Tisnado:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
f,
LEN(
x
),
t,
MID(
x,
SEQUENCE(
f
),
1
),
e,
LAMBDA(
x,
EXPAND(
x,
f,
,
""
)
),
l,
FILTER(
t,
ISERROR(
--t
)
),
n,
IFERROR(
TOCOL(
--t,
2
),
""
),
CONCAT(
e(
l
)&e(
n
)
)
)
)
)Excel solution 17 for Alternate Letters and Numbers, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,LAMBDA(a,LET(b,MID(a,SEQUENCE(LEN(a)),1),CONCAT(IFNA(HSTACK(FILTER(b,ISERR(b+0),""),FILTER(b,ISNUMBER(b+0),"")),"")))))Excel solution 18 for Alternate Letters and Numbers, proposed by Rayan S.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
n,
FILTER(
s,
CODE(
s
)<60
),
t,
FILTER(
s,
CODE(
s
)>60
),
arr,
VSTACK(
HSTACK(
SEQUENCE(
COUNTA(
n
),
,
2,
2
),
n
),
HSTACK(
SEQUENCE(
COUNTA(
t
),
,
1,
2
),
t
)
),
IFERROR(
TEXTJOIN(
"",
,
TAKE(
SORT(
arr
),
,
-1
)
),
x
)
)
)
)Excel solution 19 for Alternate Letters and Numbers, proposed by Hazem Hassan:
=MAP(
A2:A10,
LAMBDA(x,
LET(
a, MID(x, SEQUENCE(LEN(x)), 1),
b, ISERR(a * 1),
IFERROR(
CONCAT(
BYROW(
IFNA(
HSTACK(
FILTER(a, b),
FILTER(a, NOT(b))
),
""
),
CONCAT
)
),
x
)
)
)
)Excel solution 20 for Alternate Letters and Numbers, proposed by Arden Nguyen, CPA:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
CONCAT(
IF(
CODE(
a
) <= 57,
a,
""
)
),
c,
CONCAT(
IF(
CODE(
a
) > 57,
a,
""
)
),
d,
MAX(
LEN(
b
),
LEN(
c
)
),
e,
REDUCE(
"",
SEQUENCE(
d
),
LAMBDA(
st,
cur,
st & MID(
c,
cur,
1
) & MID(
b,
cur,
1
)
)
),
e
)
)
)Excel solution 21 for Alternate Letters and Numbers, proposed by Luis Couto:
=MAP(A2:A10,LAMBDA(f,LET(s,SEQUENCE(LEN(f)),e,MID(f,s,1),CONCAT(TOCOL(HSTACK(FILTER(e,e>"9",""),FILTER(e,NOT(e>"9"),"")),3)))))Solving the challenge of Alternate Letters and Numbers with Python
Python solution 1 for Alternate Letters and Numbers, proposed by Jan Willem Van Holst:
In Phython:
import pandas as pd
df = pd.read_csv(r"C:JWLENOVOPYTHONExcel_Challenge_366.csv", sep=";")
mylist=df[df.columns[0]].to_list()
def fx(word):
unpack = [*word]
numbers = [x for x in unpack if x.isnumeric()]
letters= [x for x in unpack if x.isalpha()]
lengthNumber = len(numbers)
lengtLetters = len(letters)
if lengthNumber > lengtLetters:
letters = letters + ['*']*(lengthNumber-lengtLetters)
else:
numbers = numbers + ['*']*(lengtLetters-lengthNumber)
result=[]
for i in range(0,len(numbers)):
result.append(letters[i])
result.append(numbers[i])
result = "".join([elem for elem in result if elem != '*'])
return result
print( [fx(elem) for elem in mylist] )
Solving the challenge of Alternate Letters and Numbers with R
R solution 1 for Alternate Letters and Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/366 Exchange Alphabets and Numbers.xlsx", range = "A1:A10")
test = read_excel("Excel/366 Exchange Alphabets and Numbers.xlsx", range = "B1:B10")
zip_string = function(string) {
letters = str_extract_all(string, "[a-zA-Z]")[[1]]
digits = str_extract_all(string, "[0-9]")[[1]]
vec_diff = abs(length(letters) - length(digits))
if (vec_diff > 0) {
if (length(letters) > length(digits)) {
digits = c(digits, rep("", vec_diff))
} else {
letters = c(letters, rep("", vec_diff))
}
}
result = map2_chr(letters, digits, function(x, y) paste0(x, y)) %>% paste0(collapse = "")
return(result)
}
result = input %>%
mutate(`Answer Expected` = map_chr(Words, zip_string)) %>%
select(-Words)
&&