Insert 1., 2., and so on before characters of each word and separate these by space. The numbers restart for each word. Hence if word is 1. “ab”, then answer would be 1.a 2.b 2. “Leo Tolstoy”, then answer would be 1.L 2.e 3.o 1.T 2.o 3.l 4.s 5.t 6.o 7.y
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 209
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Number Each Character with Power Query
Power Query solution 1 for Number Each Character, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
Text.Split([Strings], " "),
(a) =>
Text.Combine(
List.Transform(
{1 .. Text.Length(a)},
each Text.From(_) & "." & Text.Middle(a, _ - 1, 1)
),
" "
)
),
" "
)
)
in
AnsPower Query solution 2 for Number Each Character, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
Solution = Table.TransformRows(
Source,
each
let
l = Text.ToList([Strings])
in
Text.Replace(
Text.Combine(
List.Transform(
List.Positions(l),
each Text.From(_ - List.PositionOf(List.FirstN(l, _ + 1), " ", 1)) & "." & l{_}
),
" "
),
"0. ",
""
)
)
in
SolutionPower Query solution 3 for Number Each Character, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Strings], " "),
b = List.Transform(a, Text.ToList),
c = List.Transform(
{0 .. List.Count(b) - 1},
(x) => List.Transform({1 .. List.Count(b{x})}, each Text.From(_) & "." & b{x}{_ - 1})
)
in
Text.Combine(List.Combine(c), " ")
)[[Answer]]
in
SolPower Query solution 4 for Number Each Character, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
rs = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Transform(
Text.Split([Strings], " "),
(x) =>
List.Transform(List.Transform({1 .. Text.Length(x)}, each Text.From(_)), each _ & ".")
),
b = List.Transform(Text.Split([Strings], " "), (x) => Text.ToList(x)),
c = Text.Replace(
Text.Combine(
List.Transform(
List.Transform(List.Zip({a, b}), each List.Zip(_)),
each Text.Combine(List.Combine(_), " ")
),
" "
),
". ",
"."
)
][c]
)
in
rsPower Query solution 5 for Number Each Character, proposed by Brian Julius:
let
Source = Table.DuplicateColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"Strings",
"Words"
),
SplitWordsToRows = Table.ExpandListColumn(
Table.TransformColumns(
Source,
{
{
"Words",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Words"
),
ToLetters = Table.ExpandListColumn(
Table.AddColumn(SplitWordsToRows, "Letters", each Text.ToList([Words])),
"Letters"
),
Group = Table.Group(
ToLetters,
{"Strings", "Words"},
{{"All", each _, type table [Strings = text, Words = nullable text, Letters = text]}}
),
AddIndex = Table.SelectColumns(
Table.AddColumn(Group, "Index", each Table.AddIndexColumn([All], "Index", 1, 1)),
"Index"
),
Expand = Table.ExpandTableColumn(
AddIndex,
"Index",
{"Strings", "Letters", "Index"},
{"Strings", "Letters", "Index.1"}
),
CombineText = Table.SelectColumns(
Table.AddColumn(Expand, "Custom", each Text.Combine({Text.From([Index.1]), [Letters]}, ".")),
{"Strings", "Custom"}
),
Regroup = Table.Group(CombineText, {"Strings"}, {{"Answer", each [Custom]}}),
Extract = Table.TransformColumns(
Regroup,
{"Answer", each Text.Combine(List.Transform(_, Text.From), " "), type text}
)
in
ExtractSolving the challenge of Number Each Character with Excel
Excel solution 1 for Number Each Character, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,TEXTJOIN(" ",,MAP(TEXTSPLIT(a," "),LAMBDA(b,LET(s,SEQUENCE(LEN(b)),TEXTJOIN(" ",,s&"."&MID(b,s,1))))))))Excel solution 2 for Number Each Character, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(,20),m,MID(TEXTSPLIT(a,," "),s,1),TEXTJOIN(" ",,REPT(s&"."&m,m>"")))))Excel solution 3 for Number Each Character, proposed by Rick Rothstein:
=MAP(A2:A10,LAMBDA(a,TRIM(REDUCE("",TEXTSPLIT(a," "),LAMBDA(a,x,LET(s,SEQUENCE(LEN(x)),a&" "&TEXTJOIN(" ",,s&"."&MID(x,s,1))))))))Excel solution 4 for Number Each Character, proposed by John V.:
=MAP(A2:A10,LAMBDA(x,LET(s,ROW(1:15),c,MID(TEXTSPLIT(x," "),s,1),TEXTJOIN(" ",,TOCOL(REPT(s&"."&c,c>""),,1)))))Excel solution 5 for Number Each Character, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(,20),r,MID(TEXTSPLIT(a,," "),s,1),TEXTJOIN(" ",,IF(r>"",s&"."&r,"")))))Excel solution 6 for Number Each Character, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(a,LET(s,ROW(1:99),r,s&"."&MID(TEXTSPLIT(a," "),s,1),TEXTJOIN(" ",,TOCOL(IFS(ISERR(-r),r),2,2)))))Excel solution 7 for Number Each Character, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(z,LET(a,MID(z,SEQUENCE(LEN(z)),1),b,SCAN(0,a,LAMBDA(x,y,IF(y=" ",0,x+1))),TEXTJOIN({"."," "},1,FILTER(HSTACK(b,a),a<>" ")))))Excel solution 8 for Number Each Character, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(S,TRIM(CONCAT(MAP(TEXTSPLIT(S," "),LAMBDA(A,CONCAT(LET(S,SEQUENCE(LEN(A)),S&"."&MID(A,S,1)&" "))))))))Excel solution 9 for Number Each Character, proposed by Timothée BLIOT:
=TRIM(MAP(A2:A10,LAMBDA(t,TEXTJOIN(" ",,MAP(TEXTSPLIT(t," "),LAMBDA(x,LET(A,MID(x,SEQUENCE(LEN(x)),1)&" ",B,SEQUENCE(LEN(x))&".",CONCAT(TOCOL(HSTACK(B,A))))))))))Excel solution 10 for Number Each Character, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10, LAMBDA(a, LET(_t, TEXTSPLIT(a, ," "), TEXTJOIN("", , MAP(_t, LAMBDA(b, LET(_c, SEQUENCE(LEN(b)),_w, MID(b,_c,1), CONCAT(_c&"."&_w&" "))))))))Excel solution 11 for Number Each Character, proposed by Duy Tùng:
=MAP(A2:A10,LAMBDA(v,LET(a,MID(v,SEQUENCE(LEN(v)),1),b,SCAN(0,a,LAMBDA(x,y,IF(y>" ",x+1,0))),TEXTJOIN(" ",,FILTER(HSTACK(b&"."&a),b)))))Excel solution 12 for Number Each Character, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(x,
LET(
_m, MID(x, SEQUENCE(LEN(x)), 1),
_num, TOROW(SCAN(0, _m, LAMBDA(a, v, IF(v = CHAR(32), 0, a + 1)))),
_tbl, IF(_m <> CHAR(32), _num & "." & _m, NA()),
_col, TOCOL(IF(MUNIT(LEN(x)), _tbl, NA()), 3),
TRIM(CONCAT(_col & " "))
)
)
)Excel solution 13 for Number Each Character, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(str,
LET(
_tsplit, TEXTSPLIT(str, , " "),
_tlen, LEN(_tsplit),
_num, DROP(REDUCE("", _tlen, LAMBDA(a, v, VSTACK(a, SEQUENCE(, v)))), 1),
_let, DROP(REDUCE("", _tsplit, LAMBDA(x, y, VSTACK(x, MID(y, SEQUENCE(, LEN(y)), 1)))), 1),
TRIM(CONCAT(TOCOL(_num & "." & _let, 3) & " "))
)
)
)Excel solution 14 for Number Each Character, proposed by Md. Zohurul Islam:
=MAP(A2:A10,LAMBDA(x,LET(
a,TEXTSPLIT(x," "),
b,MAP(a,LAMBDA(y,LET(sq,SEQUENCE(LEN(y)),p,MID(y,sq,1),q,TEXTJOIN(" ",,sq&"."&p),q))),
d,TEXTJOIN(" ",,b),
d)))Excel solution 15 for Number Each Character, proposed by Pieter de B.:
=MAP(A2:A10,LAMBDA(a,REDUCE("",TEXTSPLIT(a," "),LAMBDA(x,y,LET(s,SEQUENCE(LEN(y)),m,MID(y,s,1),TEXTJOIN(" ",,x,s&"."&m))))))Excel solution 16 for Number Each Character, proposed by Charles Roldan:
=LET(M, LAMBDA(f, LAMBDA(x, MAP(x, f))),
J, LAMBDA(x, TEXTJOIN(" ", , x)),
S, LAMBDA(x, SEQUENCE(LEN(x))),
A, M(LAMBDA(x, J(S(x) & "." & MID(x, S(x), 1)))),
f, M(LAMBDA(x, J(A(TEXTSPLIT(x, , " "))))), f(A2:A10))Excel solution 17 for Number Each Character, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(A2:A10;LAMBDA(b;TEXTJOIN(;;TEXTSPLIT(CONCAT(MAP(TOCOL(TEXTSPLIT(b;" "));LAMBDA(a;TEXTJOIN(;;VSTACK(TEXTJOIN(;;SEQUENCE(LEN(a))&"/");"?")))));"?";"/";TRUE;0;1)&"."&LET(x;MID(b;SEQUENCE(LEN(b));1);FILTER(x;x<>" "))&" ")))Excel solution 18 for Number Each Character, proposed by Stefan Olsson:
=BYROW(A2:A10,
LAMBDA(string,
REDUCE("", SPLIT(string, " ", 1, 1),
LAMBDA(ans, word,
ans®EXREPLACE(word, REPT("(.)", LEN(word)), REDUCE("", SEQUENCE(LEN(word)), LAMBDA(c, n, c&n&".$"&n&" ")))
))))Excel solution 19 for Number Each Character, proposed by Julien Lacaze:
=MAP(A2:A10,LAMBDA(a,TEXTJOIN(" ",1,MAP(TEXTSPLIT(a," "),LAMBDA(b,TEXTJOIN(" ",1,SEQUENCE(LEN(b))&"."&MID(b,SEQUENCE(LEN(b)),1)))))))Excel solution 20 for Number Each Character, proposed by Giorgi Goderdzishvili:
=LET(
string,A2,
seq, SEQUENCE(,LEN(string)),
chars,MID(string,SEQUENCE(,LEN(string)),1),
scanning, SCAN(0,chars, LAMBDA(x,y, IF(y=" ",0,x+1))),
maping, MAP(scanning,chars, LAMBDA(s,c, IF(c=" ", "", s&"."&c&" "))),
REPLACE(CONCAT(maping),LEN(CONCAT(maping)),1,""))Excel solution 21 for Number Each Character, proposed by Guillermo Arroyo:
=MAP(A2:A10;LAMBDA(a;TEXTJOIN(" ";;MAP(TEXTSPLIT(a;" ");LAMBDA(b;LET(l;SEQUENCE(LEN(b));TEXTJOIN(" ";;l&"."&MID(b;l;1))))))))Excel solution 22 for Number Each Character, proposed by Daniel Garzia:
=MAP(A2:A10,LAMBDA(w,TEXTJOIN(" ",,MAP(TEXTSPLIT(w," "),LAMBDA(x,LET(s,SEQUENCE(LEN(x)),TEXTJOIN(" ",,s&"."&MID(x,s,1))))))))Excel solution 23 for Number Each Character, proposed by Quadri Olayinka Atharu:
=MAP(A2:A10,LAMBDA(x,
LET(_sp,TEXTSPLIT(x," "),
_l,LEN(_sp),
_c,DROP(REDUCE(0,_l,LAMBDA(a,b,VSTACK(a,SEQUENCE(b)))),1)&".",
_ns,CONCAT(_sp),
_t,MID(_ns,SEQUENCE(LEN(_ns)),1),
TEXTJOIN(" ",1,_c&_t))))
WHERE
_sp, SPLITTED TEXT BY SPACES,
_l, LENGHT OF SPLITTED TEXT(_sp)Excel solution 24 for Number Each Character, proposed by Anup Kumar:
=BYROW(A2:A10,LAMBDA(a, LET(
txt,a,
splttxt,TEXTSPLIT(txt,," "),
TEXTJOIN(" ",,MAP(splttxt,
LAMBDA(arr,
TEXTJOIN(" ",,SEQUENCE(LEN(arr))&"."&MID(arr,SEQUENCE(LEN(arr)),1)))))
)))Excel solution 25 for Number Each Character, proposed by Rayan S.:
=MAP(A2:A10,LAMBDA(arr,LET(split, TRANSPOSE(TEXTSPLIT(arr, " ")), TEXTJOIN(" ",,MAP(split, LAMBDA(x, TEXTJOIN(" ",,SEQUENCE(LEN(x))&"."&MID(x, SEQUENCE(LEN(x)), 1))))))))Excel solution 26 for Number Each Character, proposed by Henriette Hamer:
=MAP(A2:A10;LAMBDA(_data;TEXTJOIN(" ";TRUE;MAP(TEXTSPLIT(_data;;" ");LAMBDA(_split;TEXTJOIN(" ";TRUE;SEQUENCE(;LEN(_split))&"."&MID(_split;SEQUENCE(;LEN(_split));1)))))))Excel solution 27 for Number Each Character, proposed by Vinay BN:
=LET(
val, A2,
sp, MID(val, SEQUENCE(LEN(val)), 1),
num, SCAN(
0,
sp,
LAMBDA(x, y, IF(y = " ", 0, x + 1))
),
fsp, FILTER(sp, sp <> " "),
fnum, FILTER(num, num <> 0),
stack, HSTACK(fnum, fsp),
join, TEXTJOIN(".", TRUE, stack),
join
)&&&