For a given string, interchange the alphabets by swapping positions given in Numbers column. For positions swapping, you will need to pick up first 2, next 2 and so on. Position starts with 1 not 0 i.e. Excel is index 1 based not index 0 based. Ex. String = nation, Numbers = 2, 3, 3, 1, 5, 2 First pair swapping for 2, 3 (start string is nation) = ntaion Second pair swapping for 3, 1 (start string is ntaion) = atnion Third pair swapping for 5, 2 (start string is atnion) = aonitn
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 570
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Swap Letters by Index Pair with Power Query
Power Query solution 1 for Swap Letters by Index Pair, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Answer Expected",
each
let
t = (a, b, c) =>
[
i = Text.ToList(a),
j = List.ReplaceRange(i, b - 1, 1, {i{c - 1}}),
k = Text.Combine(List.ReplaceRange(j, c - 1, 1, {i{b - 1}}))
][k],
u = List.Transform(Text.Split([Numbers], ","), Number.FromText),
v = List.Accumulate(
List.Split(u, 2),
[String],
(m, n) => if List.Count(n) = 2 then t(m, n{0}, n{1}) else m
)
in
v
)
in
BPower Query solution 2 for Swap Letters by Index Pair, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
L = Text.Length([String]),
N = Text.Split([Numbers], ", "),
T = List.Transform(N, Number.From),
S = List.Split(T, 2),
R = List.Accumulate(
S,
[String],
(s, c) =>
[
rp = List.ReplaceMatchingItems({1 .. L}, List.Distinct({c} & {List.Reverse(c)})),
nx = List.Transform(rp, (f) => Text.At(s, f - 1)),
r = Text.Combine(nx)
][r]
)
][R]
)
in
ReturnPower Query solution 3 for Swap Letters by Index Pair, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.ToList([String]),
b = List.Positions(a),
c = List.Split(List.Transform(Text.Split([Numbers], ", "), each Number.From(_) - 1), 2),
z = List.Accumulate(
c,
a,
(x, y) =>
let
e = List.Reverse(y),
f =
if y{0} = y{1} then
x
else
List.Transform(
List.Sort(
List.Zip({x, List.ReplaceMatchingItems(b, List.Zip({y, e}))}),
each _{1}
),
each _{0}
)
in
f
)
in
Text.Combine(z)
)
in
SolPower Query solution 4 for Swap Letters by Index Pair, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = List.Transform(Text.Split([Numbers], ", "), each Number.From(_)),
b = List.Accumulate(
List.Split(a, 2),
[String],
(x, y) =>
[
r = Text.Middle(x, y{0} - 1, 1),
s = Text.Middle(x, y{1} - 1, 1),
t = Text.ReplaceRange(x, y{0} - 1, 1, s),
u = Text.ReplaceRange(t, y{1} - 1, 1, r)
][u]
)
][b]
),
Result = Table.AddColumn(AddCol, "Check", each [Answer Expected] = [My Answer])
in
ResultPower Query solution 5 for Swap Letters by Index Pair, proposed by Rafael González B.:
let
Source = Table,
Fx_Switch = (T1, T2) =>
let
St = T1,
Nu = T2,
LL = List.Last, TA = Text.At, TRR = Text.ReplaceRange,
Pos = let
a = Text.Split(Nu, ", "),
b = List.Transform(a, each Number.From(_) - 1),
c = List.Split(b, 2)
in
c,
Lt = List.Accumulate(Pos,
{St},
(x,y) =>
let
n = LL(x),
o = TA(n, y{0}),
p = TA(n, y{1}),
q = TRR(n, y{1}, 1, o),
r = TRR(q, y{0}, 1, p)
in
x & {r} )
in
LL(Lt),
Ans= Table.AddColumn(Source, "Answer Expected", each Fx_Switch([String], [Numbers]))[[Answer Expected]]
in
Ans
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 6 for Swap Letters by Index Pair, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (w, x, y, z) =>
Text.ReplaceRange(w, Number.From(y) - 1, 1, Text.Range(x, Number.From(z) - 1, 1)),
C = List.Transform(
Table.ToRows(A),
(x) =>
List.Accumulate(
List.Split(Text.Split(x{1}, ", "), 2),
x{0},
(s, c) => B(B(s, s, c{0}, c{1}), s, c{1}, c{0})
)
)
in
CSolving the challenge of Swap Letters by Index Pair with Excel
Excel solution 1 for Swap Letters by Index Pair, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,B2:B10,LAMBDA(t,x,LET(q,SEQUENCE(20),CONCAT(REDUCE(MID(t,q,1),q,LAMBDA(s,i,LET(x,--INDEX(TEXTSPLIT(x,","),i*2-{1;0}),SORTBY(s,IFNA(XLOOKUP(q,x,INDEX(x,{2;1})),q)))))))))Excel solution 2 for Swap Letters by Index Pair, proposed by John V.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
n,
TEXTSPLIT(
b,
,
","
),
r,
REPLACE,
REDUCE(
a,
SEQUENCE(
ROWS(
n
)/2
),
LAMBDA(
a,
v,
LET(
o,
INDEX(
n,
2*v-1
),
c,
INDEX(
n,
2*v
),
r(
r(
a,
o,
1,
MID(
a,
c,
1
)
),
c,
1,
MID(
a,
o,
1
)
)
)
)
)
)
)
)Excel solution 3 for Swap Letters by Index Pair, proposed by 🇰🇷 Taeyong Shin:
=LET(n,B2:B10,m,MAX(LEN(REGEXREPLACE(n,"d+(?:, )?",1))),r,--(0®EXREPLACE(n,"(d+)"&REPT("(?:, (d+))?",m),"$"&SEQUENCE(,m))),REDUCE(A2:A10,SEQUENCE(m/2,,,2),LAMBDA(a,v,LET(c,INDEX(r,,v),i,INDEX(r,,v+1),IF(c,REPLACE(REPLACE(a,c,1,MID(a,i,1)),i,1,MID(a,c,1)),a)))))Excel solution 4 for Swap Letters by Index Pair, proposed by Kris Jaganah:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
v,
w,
REDUCE(
v,
BYROW(
SORTBY(
WRAPROWS(
TEXTSPLIT(
w,
", "
),
2
),
{2,
1}
),
ARRAYTOTEXT
),
LAMBDA(
x,
y,
LET(
a,
--TEXTSPLIT(
y,
,
", "
),
b,
TAKE(
a,
1
),
c,
DROP(
a,
1
),
d,
REPLACE(
REPLACE(
x,
b,
1,
MID(
x,
c,
1
)
),
c,
1,
MID(
x,
b,
1
)
),
d
)
)
)
)
)Excel solution 5 for Swap Letters by Index Pair, proposed by Julian Poeltl:
=MAP(A2:A10,B2:B10,LAMBDA(S,N,TAKE(SCAN(S,BYROW(WRAPROWS(TEXTSPLIT(N,","),2),ARRAYTOTEXT),LAMBDA(A,B,LET(T,--TEXTBEFORE(B,";"),R,TEXTAFTER(B,";"),REPLACE(REPLACE(A,T,1,MID(A,R,1)),R,1,MID(A,T,1))))),-1)))Excel solution 6 for Swap Letters by Index Pair, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
B2:B10,
LAMBDA(a, b,
LET(
s, TEXTSPLIT(b, ", "),
wr, WRAPROWS(s, 2),
th, BYROW(wr, LAMBDA(x, LAMBDA(x))),
e, LAMBDA(v, w, x, y, z, REPLACE(w, TAKE(x(), , y), 1, MID(v, TAKE(x(), , z), 1))),
rdc, REDUCE(a, th, LAMBDA(x, y, e(x, e(x, x, y, 1, -1), y, -1, 1))),
rdc
)
)
)Excel solution 7 for Swap Letters by Index Pair, proposed by Timothée BLIOT:
=MAP(A2:A10,B2:B10,LAMBDA(i,j,LET(A,WRAPROWS(--TEXTSPLIT(j,", "),2), REDUCE(i,SEQUENCE(ROWS(A)),LAMBDA(w,v,LET(B,INDEX(A,v,),C,SEQUENCE(LEN(w)),D,MID(w,C,1),E,TAKE(B,,1),F,TAKE(B,,-1),CONCAT(XLOOKUP(MAP(C,LAMBDA(x,IF(ISNUMBER(XMATCH(x,B)),XLOOKUP(x,VSTACK(E,F),VSTACK(F,E)),x))),C,D))))))))Excel solution 8 for Swap Letters by Index Pair, proposed by Hussein SATOUR:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
z,
w,
LET(
I,
INDEX,
R,
REPLACE,
N,
BYROW(
WRAPROWS(
TEXTSPLIT(
w,
", "
),
2
)&"/",
CONCAT
),
REDUCE(
z,
N,
LAMBDA(
x,
y,
LET(
p,
TEXTSPLIT(
y,
"/"
),
T,
TAKE(
x,
-1
),
d,
I(
p,
,
1
),
e,
I(
p,
,
2
),
b,
MID(
T,
d,
1
),
c,
MID(
T,
e,
1
),
R(
R(
T,
d,
1,
c
),
e,
1,
b
)
)
)
)
)
)
)Excel solution 9 for Swap Letters by Index Pair, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
R,
REPLACE,
t,
WRAPROWS(
TEXTSPLIT(
b,
", "
),
2
),
REDUCE(
a,
SEQUENCE(
ROWS(
t
)
),
LAMBDA(
i,
x,
LET(
y,
INDEX(
t,
x,
1
),
z,
INDEX(
t,
x,
2
),
R(
R(
i,
y,
1,
MID(
i,
z,
1
)
),
z,
1,
MID(
i,
y,
1
)
)
)
)
)
)
)
)Excel solution 10 for Swap Letters by Index Pair, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(x, y,
LET(
_w, WRAPROWS(TEXTSPLIT(y, , ", "), 2) + 0,
REDUCE(
x,
SEQUENCE(ROWS(_w)),
LAMBDA(a, v,
LET(
_a, INDEX(_w, v, 1),
_b, INDEX(_w, v, 2),
REPLACE(REPLACE(a, _a, 1, MID(a, _b, 1)), _b, 1, MID(a, _a, 1))
)
)
)
)
)
)Excel solution 11 for Swap Letters by Index Pair, proposed by Bilal Mahmoud kh.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
i,
j,
LET(
a,
--TEXTSPLIT(
j,
","
),
REDUCE(
i,
SEQUENCE(
COUNT(
a
)/2,
,
1,
2
),
LAMBDA(
x,
y,
LET(
n,
MID(
x,
INDEX(
a,
1,
y
),
1
),
& m,
MID(
x,
INDEX(
a,
1,
y+1
),
1
),
REPLACE(
REPLACE(
x,
INDEX(
a,
1,
y
),
1,
m
),
INDEX(
a,
1,
y+1
),
1,
n
)
)
)
)
)
)
)Excel solution 12 for Swap Letters by Index Pair, proposed by JvdV –:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
REDUCE(
a,
BYROW(
WRAPROWS(
TEXTSPLIT(
b,
","
),
2
)-1,
LAMBDA(
x,
"^.{"&MIN(
x
)&"}K(.)(.{"&MAX(
x
)-MIN(
x
)-1&"})(.)"
)
),
LAMBDA(
y,
z,
REGEXREPLACE(
y,
z,
"$3$2$1"
)
)
)
)
)Excel solution 13 for Swap Letters by Index Pair, proposed by Songglod P.:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,REDUCE(a,BYROW(WRAPROWS(TEXTSPLIT(b,", "),2),LAMBDA(r,ARRAYTOTEXT(r))),LAMBDA(a,v,LET(l,TEXTBEFORE(v,", "),r,TEXTAFTER(v,", "),lc,MID(a,l,1),rc,MID(a,r,1),tmp,REPLACE(a,l,1,rc),REPLACE(tmp,r,1,lc))))))Solving the challenge of Swap Letters by Index Pair with Python
Python solution 1 for Swap Letters by Index Pair, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "570 Position Swapping.xlsx"
input = pd.read_excel(path,usecols="A:B", nrows=10)
test = pd.read_excel(path,usecols="C", nrows=10)
def swap_string_tidy(string, numbers):
str_list = list(string)
indices = [int(num) - 1 for num in numbers.split(',')]
for i in range(0, len(indices), 2):
str_list[indices[i]], str_list[indices[i+1]] = str_list[indices[i+1]], str_list[indices[i]]
return ''.join(str_list)
input['Swapped'] = input.apply(lambda row: swap_string_tidy(row['String'], row['Numbers']), axis=1)
print(input['Swapped'].equals(test['Answer Expected'])) # True
Solving the challenge of Swap Letters by Index Pair with Python in Excel
Python in Excel solution 1 for Swap Letters by Index Pair, proposed by Alejandro Campos:
def swap_positions(string, numbers):
chars = list(string)
for i in range(0, len(numbers), 2):
pos1 = numbers[i] - 1
pos2 = numbers[i + 1] - 1
chars[pos1], chars[pos2] = chars[pos2], chars[pos1]
return ''.join(chars)
strings = xl("A2:A10")[0]
numbers_dict = xl("B1:B10", headers=True)
numbers_list = [list(map(int, x.split(', '))) for x in numbers_dict['Numbers']]
results = [swap_positions(strings[i], numbers_list[i]) for i in range(len(strings))]
df = pd.DataFrame({
'Original String': strings,
'Numbers': numbers_dict['Numbers'],
'Resulting String': results
})
df
Python in Excel solution 2 for Swap Letters by Index Pair, proposed by Anshu Bantra:
df = xl("A1:B10", headers=True)
answer = ['Answer']
for df_idx, num_set in enumerate(df['Numbers']):
strg = [*df['String'][df_idx]]
num_set_ = num_set.split(', ')
for idx in range(0,len(num_set_),2):
x, y = int(num_set_[idx])-1, int(num_set_[idx+1])-1
strg[x], strg[y] = strg[y], strg[x]
answer.append(''.join(strg))
answer
Python in Excel solution 3 for Swap Letters by Index Pair, proposed by Owen Price:
https://www.linkedin.com/posts/owenhprice_pythoninexcel-python-excel-activity-7254525669009674240-14Em?utm_source=share&utm_medium=member_androidSolving the challenge of Swap Letters by Index Pair with R
R solution 1 for Swap Letters by Index Pair, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/570 Position Swapping.xlsx"
input = read_excel(path, range = "A1:B10")
test = read_excel(path, range = "C1:C10")
swap_string = function(string, numbers) {
numbers = as.numeric(str_split(numbers, ",\s*")[[1]])
str_vec = str_split(string, "", simplify = TRUE)
for (i in seq(1, length(numbers), by = 2)) {
str_vec[c(numbers[i], numbers[i + 1])] = str_vec[c(numbers[i + 1], numbers[i])]
}
str_c(str_vec, collapse = "")
}
result = input %>%
mutate(Swapped = map2_chr(String, Numbers, swap_string))
all.equal(result$Swapped, test$`Answer Expected`)
# [1] TRUE
&&