Replace next set of numbers with previous set of numbers and first set of numbers will be replaced by last set of numbers. Ex. mn456q12 => mn12q456 x7y09z123 => x123y7z09
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 643
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Swap Number Sequences with Power Query
Power Query solution 1 for Swap Number Sequences, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Alphabet = {"a" .. "z"} & {"A" .. "Z"},
Transform = Table.AddColumn(
Source,
"Answer Expected",
each
let
a = [Strings],
b = List.Select(Text.SplitAny(a, Text.Combine(Alphabet, "")), each _ <> ""),
c = {List.Last(b)} & List.RemoveLastN(b),
d = List.Zip({b, c}),
e = Table.FromList(Text.ToList(a), Splitter.SplitByNothing()),
Fx = (value) => try Value.Type(Number.From(value)) otherwise Value.Type(value),
f = Table.Group(
e,
"Column1",
{{"All", each Text.Combine([Column1], "")}},
0,
(x, y) => Number.From(Fx(y) <> Fx(x))
)[All],
g = Text.Combine(List.ReplaceMatchingItems(f, d), "")
in
g
)
in
TransformPower Query solution 2 for Swap Number Sequences, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Custom",
each
let
k = Splitter.SplitTextByCharacterTransition,
a = k({"A" .. "Z"} & {"a" .. "z"}, {"0" .. "9"})([Strings]),
b = List.Transform(a, each k({"0" .. "9"}, {"A" .. "Z"} & {"a" .. "z"})(_)),
c = List.Combine(b),
d = List.Zip({c, List.Positions(c)}),
e = List.Select(d, each List.ContainsAny(Text.ToList(_{0}), {"0" .. "9"})),
f = List.Zip(
{
{List.Last(e){0}} & List.RemoveLastN(List.Transform(e, each _{0})),
List.Transform(e, each _{1})
}
),
g = List.Sort(List.Difference(d, e) & f, each _{1}),
h = Text.Combine(List.Transform(g, each _{0}))
in
h
)
in
SolPower Query solution 3 for Swap Number Sequences, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = Table.TransformColumnTypes(S, {"Strings", type text})[Strings],
Fx = (x) =>
let
b = LT(Text.ToList(x), each try Number.From(_) otherwise _),
c = Table.AddColumn(Table.FromColumns({b}), "A", each if [Column1] is text then 0 else 1),
d = Table.Group(c, "A", {"G", each [Column1]}, 0)[G],
e = LT(d, each Text.Combine(LT(_, Text.From))),
f = LT(e, each try Number.From(_) otherwise _),
g = List.Alternate(f, 1, 1, 1),
h = List.Difference(f, g),
i = try if g{0} is number then List.RemoveLastN({List.Last(g)} & g) else g otherwise {},
j = try if h{0} is number then List.RemoveLastN({List.Last(h)} & h) else h otherwise {},
k = Text.Combine(LT(List.Combine(List.Zip({i, j})), Text.From))
in
k,
Sol = Table.FromColumns({LT(a, each Fx(_))}, {"Answer Expected"})
in
SolPower Query solution 4 for Swap Number Sequences, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Temp", Fun1),
Fun1 = each [
A = List.Select(Text.SplitAny([Strings], Text.Combine({"0" .. "9"})), each _ <> ""),
B = List.Select(
Text.SplitAny([Strings], Text.Combine({"a" .. "z"} & {"A" .. "Z"})),
each _ <> ""
),
C = {List.Last(B)} & List.RemoveLastN(B)
],
Expand = Table.ExpandRecordColumn(AddCol, "Temp", {"A", "C"}, {"S", "N"}),
Res = Table.AddColumn(Expand, "Answer Expected", Fun2)[[Strings], [Answer Expected]],
Fun2 = each [
A =
if List.Contains({"0" .. "9"}, Text.Start([Strings], 1)) then
List.Zip({[N], [S]})
else
List.Zip({[S], [N]}),
B = Text.Combine(List.Combine(A))
][B]
in
ResPower Query solution 5 for Swap Number Sequences, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
s = Table.AddColumn(
Source,
"r",
each [
a = List.Select(
Text.SplitAny(Text.Lower([Strings]), Text.Combine({"a" .. "z"})),
(x) => x <> ""
),
b = List.Select(
Text.Split(List.Accumulate(a, [Strings], (s, c) => Text.Replace(s, c, " ")), " "),
(x) => x <> ""
),
c = {List.Last(a)} & List.RemoveLastN(a, 1),
d = Value.FromText(Text.Start([Strings], 1)) is number,
e = if d then Table.FromColumns({c, b}) else Table.FromColumns({b, c}),
f = Text.Combine(Table.AddColumn(e, "z", each Text.Combine({[Column1], [Column2]}))[z])
][f]
)[r]
in
sPower Query solution 6 for Swap Number Sequences, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
fctMatch = (txt, pattern) =>
Text.Split(
Web.Page(
"<script>
x = '"
& txt
& "'; y = '"
& pattern
& "';
document.write(x.match(RegExp(y,'g')));
</script>"
){0}[Data]{0}[Children]{1}[Children]{0}[Text],
","
),
fctSearch = (txt, pattern) =>
Web.Page(
"<script>
x = '"
& txt
& "'; y = '"
& pattern
& "';
document.write(x.search(RegExp(y,'g')));
</script>"
){0}[Data]{0}[Children]{1}[Children]{0}[Text],
r = Table.AddColumn(
Source,
"s",
each [
nr = fctMatch([Strings], "[0-9]+"),
abc = fctMatch([Strings], "[A-Za-z]+"),
a = List.Transform({List.Count(nr) - 1} & {0 .. List.Count(nr) - 2}, (x) => nr{x}),
fct = (l as list) =>
Text.Combine(
List.Transform(List.Zip(l), (x) => Text.Combine(List.Select(x, each _ <> "null")))
),
c = if fctSearch([Strings], "[0-9]") = "0" then fct({a, abc}) else fct({abc, a})
][c]
)[s]
in
rSolving the challenge of Swap Number Sequences with Excel
Excel solution 1 for Swap Number Sequences, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A8,LAMBDA(a,LET(b,REGEXEXTRACT(a,"d+|D+",1),n,COUNTA(b),s,SEQUENCE(,n),CONCAT(SORTBY(SORTBY(b,b<"a"),SORTBY(s,(b<"a")*(MOD(s-3,n)+1)))))))Excel solution 2 for Swap Number Sequences, proposed by Rick Rothstein:
=MAP(
A2:A8,
LAMBDA(
r,
LET(
n,
TEXTSPLIT(
r,
,
CHAR(
SEQUENCE(
26,
,
97
)
),
1,
1
),
z,
TAKE(
DROP(
VSTACK(
n,
n
),
COUNT(
-n
)-1
),
COUNT(
-n
)
),
t,
TEXTSPLIT(
r,
,
SEQUENCE(
10,
,
0
),
1
),
IFERROR(
CONCAT(
IFNA(
IF(
ISNUMBER(
-LEFT(
r
)
),
HSTACK(
z,
t
),
HSTACK(
t,
z
)
),
""
)
),
r
)
)
)
)Excel solution 3 for Swap Number Sequences, proposed by John V.:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
r,
REGEXEXTRACT,
h,
HSTACK,
i,
TOCOL(
r(
x,
"d+",
1
)
),
c,
ROWS(
i
),
n,
SORTBY(
i,
MOD(
SEQUENCE(
c
),
c
)
),
t,
TOCOL(
r(
x&" ",
"D+",
1
)
),
TRIM(
CONCAT(
IFNA(
IF(
LEFT(
x
)<"a",
h(
n,
t
),
h(
t,
n
)
),
""
)
)
)
)
)
)Excel solution 4 for Swap Number Sequences, proposed by Kris Jaganah:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
CHAR(
SEQUENCE(
58,
,
65
)
),
1
),
b,
IFERROR(
TEXTSPLIT(
x,
,
a,
1,
,
""
),
""
),
c,
IFERROR(
VSTACK(
TAKE(
a,
-1
),
DROP(
a,
-1
)
),
a
),
CONCAT(
IFNA(
IF(
LEFT(
@b
)=LEFT(
x
),
HSTACK(
b,
c
),
HSTACK(
c,
b
)
),
""
)
)
)
)
)Excel solution 5 for Swap Number Sequences, proposed by Timothée BLIOT:
=MAP(A2:A8,LAMBDA(z,LET(A,REGEXEXTRACT(z,"D+|d+",1),S,SEQUENCE(,COLUMNS(A)),I,INDEX,J,ISNUMBER,B,MAP(A,S,LAMBDA(x,y,IF(J(--x),SUM(--(J(--TAKE(A,1,y)))),0))),C,IF(B<>0,IF(B=1,MAX(B),B-1),0),CONCAT(MAP(S, LAMBDA(x,IF(J(--I(A,,x)),I(A,,XMATCH(I(C,,x),B)),I(A,,x))))))))Excel solution 6 for Swap Number Sequences, proposed by Md. Zohurul Islam:
=MAP(A2:A8,LAMBDA(x,
LET(w,LAMBDA(p,q,TOCOL(TEXTSPLIT(p,,q,1,1))),
u,CHAR(SEQUENCE(26,,97)),
v,CHAR(SEQUENCE(10,,48)),
num,IFERROR(w(x,u),""),
txt,IFERROR(w(x,v),""),
a,VSTACK(TAKE(num,-1),DROP(num,-1)),
b,HSTACK(a,txt),
c,HSTACK(txt,a),
d,LEFT(x,1),
f,IF(ISERROR(ABS(d)),c,b),
g,IFNA(f,""),
h,BYROW(g,CONCAT),
i,IFERROR(CONCAT(h),x),
i)))Excel solution 7 for Swap Number Sequences, proposed by Pieter de B.:
=LET(
x,
TEXTSPLIT,
v,
VSTACK,
s,
SEQUENCE,
MAP(
A2:A8,
LAMBDA(
a,
LET(
r,
IF(
ISERR(
--RIGHT(
a
)
),
"",
" "
),
t,
x(
a,
,
s(
10
)-1,
1,
,
a
),
n,
x(
a,
,
t,
1,
,
a
),
y,
ROWS(
n
),
TRIM(
IFERROR(
TEXTJOIN(
SORTBY(
n,
MOD(
s(
y
),
y
)
),
,
IF(
ISERR(
--LEFT(
a
)
),
v(
t,
r
),
v(
" ",
t,
r
)
)
),
a
)
)
)
)
)
)Excel solution 8 for Swap Number Sequences, proposed by ferhat CK:
=MAP(
A2:A8,
LAMBDA(
v,
IFERROR(
LET(
w,
REGEXEXTRACT(
v,
"d+",
1
),
q,
HSTACK(
DROP(
w,
,
1
),
TAKE(
w,
,
1
)
),
TAKE(
SCAN(
v,
w&"-"&q,
LAMBDA(
x,
y,
SUBSTITUTE(
x,
TEXTAFTER(
y,
"-"
),
TEXTBEFORE(
y,
"-"
),
1
)
)
),
,
-1
)
),
v
)
)
)Excel solution 9 for Swap Number Sequences, proposed by JvdV -:
=REGEXREPLACE(
A2:A8,
"^D*+K(?=.*D(d+))|(d+)(D++)B(d*(D*)$)?",
"$1$3$2$5"
)
For those wondering:
* ^D*+K(
?=.*D(
d+
)
) - Here we grab the last group of numbers and make sure we bring them to the front;
* (
d+
)(D++)B(d*(D*)$)Excel solution 10 for Swap Number Sequences, proposed by Fausto Bier:
=MAP(A2:A8,LAMBDA(y,LET(r,REGEXEXTRACT(y,"d+|D+",1),e,SEQUENCE(,COLUMNS(r))*(1/ISNUMBER(--r)),t,TOROW(e,3),CONCAT(IFERROR(LOOKUP(e,t,XLOOKUP(HSTACK(MAX(t),DROP(t,,-1)),e,r)),r)))))Solving the challenge of Swap Number Sequences with Python in Excel
_x000D_Python in Excel solution 1 for Swap Number Sequences, proposed by Seokho MOON:
import re
from itertools import zip_longest
def rearrange_text(txt):
numbers = re.findall("d+", txt)
letters = re.findall("D+", txt)
if not numbers:
return letters[0]
if not letters:
return numbers[0]
numbers = [numbers[-1]] + numbers[:-1]
if txt[0].isdigit():
merged = zip_longest(numbers, letters, fillvalue="")
else:
merged = zip_longest(letters, numbers, fillvalue="")
return "".join("".join(pair) for pair in merged)
df["Answer Expected"] = df["Strings"].apply(rearrange_text)
df
Solving the challenge of Swap Number Sequences with R
_x000D_R solution 1 for Swap Number Sequences, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/643 Shift Numbers by One Position.xlsx"
input = read_excel(path, range = "A1:A8")
test = read_excel(path, range = "B1:B8")
shift_digits <- function(s) {
split <- str_split(s, "(?<=\d)(?=\D)|(?<=\D)(?=\d)", simplify = TRUE)
digit_idxs <- which(str_detect(split, "\d"))
if (length(digit_idxs) > 0) {
shifted_digits <- split[digit_idxs] %>%
{c(tail(., 1), head(., -1))}
split[digit_idxs] <- shifted_digits
}
str_c(split, collapse = "")
}
result = input %>%
mutate(answer = map_chr(Strings, shift_digits))
all.equal(result$answer, test$`Answer Expected`)
#> [1] TRUE