Split the IDs from the beginning of the text up to the “|” character in each occurrence of “|”.
📌 Challenge Details and Links
Challenge Number: 146
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Column Splitting! Part 2 with Power Query
Power Query solution 1 for Column Splitting! Part 2, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
F = each Text.Split(_, "|"),
S = Table.SplitColumn(
Source,
"ID",
each List.Transform(List.Positions(F(_)), (p) => Text.Combine(List.FirstN(F(_), p + 1))),
List.Max(List.Transform(Source[ID], each List.Count(F(_))))
)
in
SPower Query solution 2 for Column Splitting! Part 2, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
tab = Table.TransformColumns(Fonte, {"ID", each
let
a = Text.Length(Text.Select(_,{"|"})),
b = Table.FromRows({List.Transform({0..a},(x)=> Text.Remove(Text.BeforeDelimiter(_,"|",x),"|")) }) in b})[ID],
cmb = Table.Combine(tab)
in
cmbPower Query solution 3 for Column Splitting! Part 2, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Length = Table.AddColumn(Source, "L", each Text.Length(Text.Select([ID], "|")) + 1),
Return = Table.SplitColumn(
Source,
"ID",
each [
Dl = {0 .. Text.Length(Text.Select(_, "|"))},
R = List.Transform(
Dl,
(f) => [s = Text.BeforeDelimiter(_, "|", f), r = Text.Remove(s, "|")][r]
)
][R],
List.Max(Length[L])
)
in
ReturnPower Query solution 4 for Column Splitting! Part 2, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Combine(Table.AddColumn(Source, "A", each
let
a = Text.Split([ID], "|"),
b = {1..List.Count(a)},
c = List.Transform(b, each Text.Combine(List.FirstN(a,_))),
d = List.Transform(b, each "ID."&Text.From(_)),
e = Table.FromRows({c},d)
in e)[A])
in
SolPower Query solution 5 for Column Splitting! Part 2, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WcoyoMTRSitUBsmoiaowMwcygYL8aiKCbW40RhBUUWGMIZEMU+BsZ1fj7K8XGAgA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [ID = _t]
),
AddNL = Table.AddColumn(
Source,
"NL",
each Table.FromRows(
{List.Skip(List.Accumulate(Text.Split([ID], "|"), {""}, (s, c) => s & {List.Last(s) & c}), 1)}
)
),
Result = Table.ExpandTableColumn(
AddNL,
"NL",
Table.ColumnNames(Table.Combine(AddNL[NL])),
List.Transform(
Table.ColumnNames(Table.Combine(AddNL[NL])),
each Text.Replace(_, "Column", "ID.")
)
)
in
ResultPower Query solution 6 for Column Splitting! Part 2, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.AddColumn(
Source,
"Data",
each Text.Combine(
List.Accumulate(Text.Split([ID], "|"), {}, (s, c) => s & {List.Last(s, "") & c}),
","
)
),
ColCount = List.Max(List.Transform(Transform[Data], each List.Count(Text.Split(_, ",")))),
Columns = List.Transform({1 .. ColCount}, each "ID." & Text.From(_)),
Result = Table.SplitColumn(Transform[[Data]], "Data", each Text.Split(_, ","), Columns)
in
ResultPower Query solution 7 for Column Splitting! Part 2, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = Table.TransformRows(
Source,
each [
a = Text.Split([ID], "|"),
b = List.Count(a),
c = {b, List.Transform({1 .. b}, each Text.Combine(List.FirstN(a, _)))}
][c]
),
FromList = Table.FromList(Rows, each _{1}, List.Max(List.Transform(Rows, each _{0}))),
Result = Table.TransformColumnNames(FromList, each Text.Replace(_, "Column", "ID."))
in
ResultPower Query solution 8 for Column Splitting! Part 2, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Ans",
each
let
a = Text.Split([ID], "|"),
b = List.Transform({1 .. List.Count(a)}, each Text.Combine(List.FirstN(a, _))),
c = Table.FromRows({b})
in
c
)[Ans],
C = Table.TransformColumnNames(Table.Combine(B), each Text.Replace(_, "Column", "ID."))
in
CPower Query solution 9 for Column Splitting! Part 2, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Header = List.Transform( {1..List.Max(List.Transform(S[ID],each Text.Length(Text.Select(_,"|"))))+1}, each "ID"&Text.From(_)),
B = Table.AddColumn(S, "C", each List.Skip( List.Accumulate(Text.Split([ID],"|"),{""},(S,C)=>S&{List.Last(S)&C}),1)),
C = Table.FromColumns(List.Zip(B[C]),Header)
in
CPower Query solution 10 for Column Splitting! Part 2, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][ID],
lst = List.Transform(
Source,
(x) => List.Accumulate(Text.Split(x, "|"), {}, (a, v) => a & {List.Last(a, "") & v})
),
Cols = List.Zip(lst),
ColNames = List.Transform({1 .. List.Count(Cols)}, each "ID." & Text.From(_))
in
Table.FromColumns(Cols, ColNames)Power Query solution 11 for Column Splitting! Part 2, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = (x)=>
[
a = Text.Split(x{0} ,"|"),
b = List.Count(a),
c = List.Transform({1..b}, each "ID." & Text.From(_)),
d = Table.FromRows({List.Generate(()=> 0, each _ < b , each _ + 1, each Text.Combine(List.FirstN(a,_ +1)))}, c)
] [d],
C = Table.Combine(Table.ToList(A,B))
in
CPower Query solution 12 for Column Splitting! Part 2, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
List = Table.AddColumn(
Source,
"List",
each List.Transform(
Text.Split([ID], "|"),
(x) => Text.BeforeDelimiter(Text.Replace([ID], "|", ""), x) & x
)
),
Table = Table.Combine(
Table.Column(
Table.AddColumn(List, "Table", each Table.Transpose(Table.FromList([List]))),
"Table"
)
),
Final = Table.PromoteHeaders(
Table.Transpose(
Table.ReplaceValue(
Table.Transpose(Table.DemoteHeaders(Table)),
"Column",
"ID.",
Replacer.ReplaceText,
{"Column1"}
)
)
)
in
FinalSolving the challenge of Column Splitting! Part 2 with Excel
Excel solution 1 for Column Splitting! Part 2, proposed by 🇰🇷 Taeyong Shin:
=LET(
n,
MAX(
LEN(
REGEXREPLACE(
B3:B8,
"w+",
)
)+1
),
REGEXREPLACE(
B3:B8,
"^(w+)"&REPT(
"(?:|(w+))?",
n-1
),
MAP(
SEQUENCE(
,
n
),
LAMBDA(
x,
"${"&x&":+"&CONCAT(
"$"&SEQUENCE(
,
x
)
)&"}"
)
)
)
)Excel solution 2 for Column Splitting! Part 2, proposed by Aditya Kumar Darak 🇮🇳:
=LET( _id,
B3:B8, _times,
LEN(
_id
) - LEN(
SUBSTITUTE(
_id,
"|",
""
)
), _max,
MAX(
_times
) + 1, _return,
SUBSTITUTE( TEXTBEFORE(
_id & "|",
"|",
SEQUENCE(
1,
_max
),
,
,
""
), "|", "" ), _return)Excel solution 3 for Column Splitting! Part 2, proposed by Oscar Mendez Roca Farell:
=LET(
F,
TEXTSPLIT,
F(
CONCAT(
REDUCE(
B3:B8,
"|",
LAMBDA(
i,
x,
SUBSTITUTE(
i,
x,
x&F(
i,
x
)
)
)
)&"-"
),
"|",
"-",
1,
,
""
)
)Excel solution 4 for Column Splitting! Part 2, proposed by Julian Poeltl:
=LET(
T,
IFNA(
DROP(
REDUCE(
"",
B3:B8,
LAMBDA(
A,
B,
VSTACK(
A,
SCAN(
"",
TEXTSPLIT(
B,
"|"
),
CONCAT
)
)
)
),
1
),
""
),
VSTACK(
"ID."&SEQUENCE(
,
COLUMNS(
T
)
),
T
)
)Excel solution 5 for Column Splitting! Part 2, proposed by Kris Jaganah:
=IFNA(
REDUCE(
"ID."&{1,
2,
3},
B3:B8,
LAMBDA(
x,
y,
VSTACK(
x,
SCAN(
,
TEXTSPLIT(
y,
"|"
),
CONCAT
)
)
)
),
""
)Excel solution 6 for Column Splitting! Part 2, proposed by Abdallah Ally:
=DROP(
IFNA(
REDUCE(
"",
B3:B8,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
y,
"|"
),
b,
SEQUENCE(
,
COUNTA(
a
)
),
c,
MAP(
b,
LAMBDA(
u,
CONCAT(
TAKE(
a,
,
u
)
)
)
),
VSTACK(
x,
c
)
)
)
),
""
),
1
)Excel solution 7 for Column Splitting! Part 2, proposed by Imam Hambali:
=LET( a,
IFNA(
TEXTSPLIT(
TEXTJOIN(
",",
1,
B3:B8&"|00"
),
"|",
","
),
"00"
), DROP(
SCAN(
,
a,
LAMBDA(
x,
y,
IF(
y="00",
"",
x&y
)
)
),
,
-1
))Excel solution 8 for Column Splitting! Part 2, proposed by Sunny Baggu:
=LET( _a,
B3:B8 & "|", _b,
SEQUENCE(
,
MAX(
LEN(
_a
) - LEN(
SUBSTITUTE(
_a,
"|",
""
)
)
)
), VSTACK( "ID." & _b, SUBSTITUTE(
TEXTBEFORE(
_a,
"|",
_b,
,
,
""
),
"|",
""
) ))Excel solution 9 for Column Splitting! Part 2, proposed by Asheesh Pahwa:
=IFNA(
REDUCE(
D2:F2,
B3:B8,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
TEXTSPLIT(
y,
"|"
),
SCAN(
"",
t,
LAMBDA(
a,
v,
a&v
)
)
)
)
)
),
""
)Excel solution 10 for Column Splitting! Part 2, proposed by Bilal Mahmoud kh.:
=IFNA(
REDUCE(
{"ID1",
"ID2",
"ID3"},
B3:B8,
LAMBDA(
x,
y,
VSTACK(
x,
REDUCE(
,
TEXTSPLIT(
y,
"|"
),
LAMBDA(
n,
m,
HSTACK(
n,
INDEX(
n,
1,
COUNTA(
n
)
)&m
)
)
)
)
)
),
""
)Excel solution 11 for Column Splitting! Part 2, proposed by ferhat CK:
=IFNA(
REDUCE(
D2:F2,
B3:B8,
LAMBDA(
a,
v,
VSTACK(
a,
SCAN(
,
TEXTSPLIT(
v,
"|"
),
CONCAT
)
)
)
),
""
)Excel solution 12 for Column Splitting! Part 2, proposed by Hamidi Hamid:
=LET(b,
B3:B8,
u,
TEXTBEFORE(
b,
"|"
),
g,
SUBSTITUTE(
TEXTBEFORE(
b,
"|",
-1
),
"|",
""
),
x,
TEXTAFTER(
b,
"|", ),
t,
SEARCH(
"|",
x
),
p,
IFERROR(
IF(
t>0,
t,
""
),
0
),
d,
IF(
p>0,
g,
g&x
),
y,
DROP(
IFERROR(
REDUCE(
0,
b,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
"|",
)
)
)
),
),
1
),
xx,
BYROW(y,
LAMBDA(a,
SUM((a>0)*1))),
z,
BYROW(
IF(
xx>=3,
y,
""
),
CONCAT
),
HSTACK(
u,
d,
z
))Excel solution 13 for Column Splitting! Part 2, proposed by Hussein SATOUR:
=LET(
a,
REDUCE(
"",
B3:B8,
LAMBDA(
x,
y,
VSTACK(
x,
SCAN(
,
TEXTSPLIT(
y,
"|"
),
CONCAT
)
)
)
),
VSTACK(
"ID."&SEQUENCE(
,
COLUMNS(
a
)
),
IFNA(
DROP(
a,
1
),
""
)
)
)Excel solution 14 for Column Splitting! Part 2, proposed by Pieter de B.:
=SUBSTITUTE(
TEXTBEFORE(
B3:B8&"|",
"|",
{1,
2,
3},
,
,
""
),
"|",)
Or more dynamic:
=LET(
b,
B3:B8,
s,
SUBSTITUTE,
s(
TEXTBEFORE(
b&"|",
"|",
SEQUENCE(
,
1+MAX(
LEN(
b
)-LEN(
s(
b,
"|",
)
)
)
),
,
,
""
),
"|", )
)Excel solution 15 for Column Splitting! Part 2, proposed by Tomasz Jakóbczyk:
=TOROW(SUBSTITUTE(LEFT(B3,
LET(f,
(MID(
B3,
SEQUENCE(
LEN(
B3
)
),
1
)="|")*(SEQUENCE(
LEN(
B3
)
)),
VSTACK(
FILTER(
f,
f>0
)-1,
LEN(
B3
)
))),
"|",
""))Solving the challenge of Column Splitting! Part 2 with Python
Python solution 1 for Column Splitting! Part 2, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "CH-146 Column Splitting.xlsx"
input = pd.read_excel(path, usecols="B", skiprows=1, nrows=7)
test = pd.read_excel(path, usecols="D:F", skiprows=1, nrows=7)
input[['ID.1', 'ID.2', 'ID.3']] = input['ID'].str.split('|', expand=True)
input[['ID.1', 'ID.2', 'ID.3']] = input[['ID.1', 'ID.2', 'ID.3']].apply(lambda col: col.fillna(pd.NA))
input['ID.2'] = input.apply(lambda row: np.NaN if pd.isna(row['ID.2']) else f"{row['ID.1']}{row['ID.2']}", axis=1)
input['ID.3'] = input.apply(lambda row: np.NaN if pd.isna(row['ID.3']) else f"{row['ID.2']}{row['ID.3']}", axis=1)
input = input.drop(columns=['ID'])
print(input.equals(test)) # TruePython solution 2 for Column Splitting! Part 2, proposed by Luan Rodrigues:
import pandas as pd
file = "CH-146 Column Splitting.xlsx"
df = pd.read_excel(file,usecols="B",skiprows=1)
def separar(tab):
a = tab.split("|")
b = ["".join(a[:x]) for x in range(1, len(a) + 1)]
return b
df['contar'] = df['ID'].apply(separar)
rst = df['contar'].apply(pd.Series)
rst.columns = [f'ID.{i+1}' for i in rst.columns]
print(rst)Solving the challenge of Column Splitting! Part 2 with Python in Excel
Python in Excel solution 1 for Column Splitting! Part 2, proposed by Alejandro Campos:
df = xl("B2:B8", headers=True)
split_df = pd.DataFrame(df['ID'].apply(lambda id_str: [id_str.split('|')[0]] +
[id_str.split('|')[0] + ''.join(id_str.split('|')[1:i+1])
for i in range(1, len(id_str.split('|')))]).tolist(),
columns=['ID.1', 'ID.2', 'ID.3']).fillna(' ')
split_dfSolving the challenge of Column Splitting! Part 2 with R
R solution 1 for Column Splitting! Part 2, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-146 Column Splitting.xlsx"
input = read_excel(path, range = "B2:B8")
test = read_excel(path, range = "D2:F8")
result = input %>%
separate(ID, into = c("ID.1", "ID.2", "ID.3"), sep = "\|", fill = "right") %>%
mutate(ID.1 = ifelse(is.na(ID.1), NA, ID.1),
ID.2 = ifelse(is.na(ID.2), NA, paste(ID.1, ID.2, sep = "")),
ID.3 = ifelse(is.na(ID.3), NA, paste(ID.2, ID.3, sep = "")))
all.equal(result, test)
#> [1] TRUE
Solving the challenge of Column Splitting! Part 2 with Google Sheets
Google Sheets solution 1 for Column Splitting! Part 2, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1691791602#gid=1691791602