Split the given text into columns.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 433
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Split Delimited Text into Columns with Power Query
Power Query solution 1 for Split Delimited Text into Columns, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.FromRows(
List.Transform(
Source[Text],
each
let
t = Text.SplitAny(_, ". :")
in
List.FirstN(t, 3) & List.LastN(t, 2)
),
{"Level1", "Level2", "Level3", "First Name", "Last Name"}
)
in
AnsPower Query solution 2 for Split Delimited Text into Columns, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Transform = List.Transform(
Source[Text],
each [
S = Text.SplitAny(_, ". :"),
N = List.LastN(S, 2),
L = List.FirstN(S, (f) => f <> ""),
C = List.Transform({1 .. List.Count(L)}, (f) => "Level" & Text.From(f)),
R = Record.FromList(L & N, C & {"First Name", "Last Name"})
][R]
),
ColNames = Record.FieldNames(List.Max(Transform, null, Record.FieldCount)),
Return = Table.FromRecords(Transform, ColNames, MissingField.UseNull)
in
ReturnPower Query solution 3 for Split Delimited Text into Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = Text.SplitAny([Text], ".: "),
b = Table.FromRows(
{List.FirstN(a, 3) & List.LastN(a, 2)},
List.Transform({"1" .. "3"}, each "Level" & _) & {"First Name", "Last Name"}
)
in
b
)[A]
)
in
SplitPower Query solution 4 for Split Delimited Text into Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.AddColumn(Source, "A", each Text.Split([Text], " : ")),
Sol = Table.FromColumns(
List.Zip(List.Transform(Split[A], each Text.ToList(Text.Remove(_{0}, "."))))
& List.Zip(List.Transform(Split[A], each Text.Split(_{1}, " "))),
List.Transform({"1" .. "3"}, each "Level" & _) & {"First Name", "Last Name"}
)
in
SolPower Query solution 5 for Split Delimited Text into Columns, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
n = List.Max(
List.Transform(List.Buffer(Fonte[Text]), each Text.Length(Text.Select(_, {".", ":"})))
),
add = Table.AddColumn(
Fonte,
"Personalizar",
each
let
a = Text.SplitAny([Text], ".: "),
b = List.Count(Text.Split(Text.AfterDelimiter([Text], ": "), " ")),
c = Table.FromRows({List.FirstN(a, n) & List.LastN(a, b)})
in
c
)[Personalizar],
res = Table.Combine(add)
in
resPower Query solution 6 for Split Delimited Text into Columns, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
SplitColon = Table.SplitColumn(
Source,
"Text",
Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false),
{"Text.1", "Text.2"}
),
SplitDot = Table.SplitColumn(
SplitColon,
"Text.1",
Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
{"Level1", "Level2", "Level3"}
),
SplitSpace = Table.SplitColumn(
SplitDot,
"Text.2",
Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),
{"First Name", "Last Name"}
)
in
SplitSpacePower Query solution 7 for Split Delimited Text into Columns, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
C = List.Count,
I = List.InsertRange,
a = List.Select(Text.SplitAny(x, ". : "), each _ <> ""),
b = if C(a) = 3 then I(I(a, 1, {""}), 2, {""}) else if C(a) = 4 then I(a, 2, {""}) else a,
c = Text.Combine(b, ",")
in
c,
d = Table.AddColumn(S, "T", each Fx([Text]))[[T]],
e = Table.SplitColumn(
d,
"T",
Splitter.SplitTextByDelimiter(","),
{"Level1", "Level2", "Level3", "First Name", "Last Name"}
),
Sol = Table.TransformColumnTypes(
e,
{{"Level1", Int64.Type}, {"Level2", Int64.Type}, {"Level3", Int64.Type}}
)
in
SolPower Query solution 8 for Split Delimited Text into Columns, proposed by Ankur Sharma:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Text", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Text.1", "Text.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Text.1", Splitter.SplitTextByEachDelimiter({" : "}, QuoteStyle.Csv, true), {"Text.1.1", "Text.1.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Text.1.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Text.1.1.1", "Text.1.1.2", "Text.1.1.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter2",{{"Text.1.1.1", "Level 1"}, {"Text.1.1.2", "Level 2"}, {"Text.1.1.3", "Level 3"}, {"Text.1.2", "First Name"}, {"Text.2", "Last Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Level 1", Int64.Type}, {"Level 2", Int64.Type}, {"Level 3", Int64.Type}, {"First Name", type text}, {"Last Name", type text}})
in
#"Changed Type"
Best Wishes!
Power Query solution 9 for Split Delimited Text into Columns, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Inserted Text After Delimiter" = Table.AddColumn(
Source,
"Name",
each Text.AfterDelimiter([Text], ": "),
type text
),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Inserted Text After Delimiter",
"Name",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"First Name", "Last Name"}
),
#"Inserted Text Before Delimiter" = Table.AddColumn(
#"Split Column by Delimiter",
"Text Before Delimiter",
each Text.BeforeDelimiter([Text], " :"),
type text
),
#"Split Column by Delimiter1" = Table.SplitColumn(
#"Inserted Text Before Delimiter",
"Text Before Delimiter",
Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
{"Level1", "Level2", "Level3"}
),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1", {"Text"}),
#"Reordered Columns" = Table.ReorderColumns(
#"Removed Columns",
{"Level1", "Level2", "Level3", "First Name", "Last Name"}
)
in
#"Reordered Columns"Power Query solution 10 for Split Delimited Text into Columns, proposed by Kamran Mumtaz 🇵🇰:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
EndResult = Table.TransformColumnTypes(
Table.ReorderColumns(
Table.RemoveColumns(
Table.SplitColumn(
Table.SplitColumn(
Table.AddColumn(
Table.AddColumn(Source, "Name", each Text.AfterDelimiter([Text], ": "), type text),
"Position",
each Text.BeforeDelimiter([Text], " :"),
type number
),
"Name",
Splitter.SplitTextByDelimiter(" "),
{"First Name", "Last Name"}
),
"Position",
Splitter.SplitTextByDelimiter("."),
{"Level1", "Level2", "Level3"}
),
"Text"
),
{"Level1", "Level2", "Level3", "First Name", "Last Name"}
),
{{"Level1", type number}, {"Level2", type number}, {"Level3", type number}}
)
in
EndResultPower Query solution 11 for Split Delimited Text into Columns, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name = "Tableau4"]}[Content],
List = List.TransformMany(
Source[Text],
(s) => {Text.SplitAny(s, ": .")},
(s, c) => List.FirstN(c, 3) & List.LastN(c, 2)
),
Result = Table.FromRows(List, {"Level 1", "Level 2", "Level 3", "First Name", "Last Name"})
in
ResultPower Query solution 12 for Split Delimited Text into Columns, proposed by Gerrie Olivier:
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Text", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Text.1", "Text.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Text.2.1"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns", "Text.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Level 1", "Level 2", "Level 3"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter2",null,"",Replacer.ReplaceValue,{"Level 2", "Level 3"})
Solving the challenge of Split Delimited Text into Columns with Excel
Excel solution 1 for Split Delimited Text into Columns, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
{".",
" ",
":"},
TEXTSPLIT(
TEXTAFTER(
@d&A2:A20,
d,
{1,
2,
3,
-2,
-1}
),
d
)
)Excel solution 2 for Split Delimited Text into Columns, proposed by John V.:
=DROP(
REDUCE(
0,
A2:A20,
LAMBDA(
a,
v,
VSTACK(
a,
HSTACK(
TAKE(
TEXTSPLIT(
v,
{".";":";" "}
),
,
3
),
TAKE(
TEXTSPLIT(
v,
" "
),
,
-2
)
)
)
)
),
1
)Excel solution 3 for Split Delimited Text into Columns, proposed by محمد حلمي:
=REDUCE(
C1:G1,
A2:A20,
LAMBDA(
a,
d,
VSTACK(
a,
HSTACK(
TAKE(
TEXTSPLIT(
d,
{" ",
".",
":"}
),
,
3
),
TEXTSPLIT(
TEXTAFTER(
d,
" ",
-2
),
" "
)
)
)
)
)Excel solution 4 for Split Delimited Text into Columns, proposed by Kris Jaganah:
=LET(
a,
A2:A20,
IFNA(
HSTACK(
REDUCE(
"Level"&{1,
2,
3},
TEXTSPLIT(
a,
":"
),
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
"."
)
)
)
),
REDUCE(
{"First",
"Last"}&" Name",
TEXTAFTER(
a,
": "
),
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
" "
)
)
)
)
),
""
)
)Excel solution 5 for Split Delimited Text into Columns, proposed by Julian Poeltl:
=VSTACK(
HSTACK(
"Level1",
"Level2",
"Level3",
"First Name",
"Last Name"
),
TEXTSPLIT(
TEXTJOIN(
"@",
,
MAP(
A2:A20,
LAMBDA(
T,
LET(
A,
LEFT(
T,
1
),
B,
TRIM(
IFERROR(
TEXTBEFORE(
TEXTAFTER(
T,
"."
),
{":",
"."}
),
""
)
),
CC,
TEXTBEFORE(
T,
":"
),
C,
IFERROR(
TRIM(
RIGHT(
CC,
LEN(
CC
)-SEARCH(
".",
CC,
SEARCH(
".",
CC
)+1
)
)
),
""
),
D,
TEXTSPLIT(
TEXTAFTER(
T,
": "
),
" "
),
TEXTJOIN(
";",
FALSE,
A,
B,
C,
D
)
)
)
)
),
";",
"@"
)
)Excel solution 6 for Split Delimited Text into Columns, proposed by Julian Poeltl:
=VSTACK(
HSTACK(
"Level1",
"Level2",
"Level3",
"First Name",
"Last Name"
),
TEXTSPLIT(
TEXTJOIN(
"@",
,
MAP(
A2:A20,
LAMBDA(
T,
LET(
NP,
TEXTSPLIT(
T,
& {".",
" ",
" : "}
),
IN,
ISNUMBER(
NP*1
),
M,
HSTACK(
EXPAND(
FILTER(
NP,
IN
),
,
3,
""
),
TAKE(
NP,
,
-2
)
),
TEXTJOIN(
";",
FALSE,
M
)
)
)
)
),
";",
"@"
)
)Excel solution 7 for Split Delimited Text into Columns, proposed by Timothée BLIOT:
=DROP(
REDUCE(
"",
A2:A20,
LAMBDA(
w,
v,
LET(
A,
TEXTSPLIT(
v,
{".",
" ",
":"}
),
VSTACK(
w,
HSTACK(
CHOOSECOLS(
A,
{1,
2,
3}
),
TAKE(
A,
,
-2
)
)
)
)
)
),
1
)Excel solution 8 for Split Delimited Text into Columns, proposed by Hussein SATOUR:
=DROP(
TEXTSPLIT(
CONCAT(
LET(
a,
LEN(
A2:A20
)-LEN(
SUBSTITUTE(
A2:A20,
".",
""
)
),
SUBSTITUTE(
A2:A20,
" : ",
IFS(
a=1,
" :",
a=2,
":",
1,
" : "
)
)&"/"
)
),
{" ",
":",
"."},
"/"
),
-1
)Excel solution 9 for Split Delimited Text into Columns, proposed by Oscar Mendez Roca Farell:
=LET(
v,
A2:A20,
F,
LAMBDA(
x,
y,
z,
SUBSTITUTE(
x,
y,
z
)
),
n,
LEN(
v
)-LEN(
F(
v,
".",
""
)
),
DROP(
TEXTSPLIT(
CONCAT(
F(
F(
v,
IF(
n-2,
":",
": "
),
IF(
n,
"",
" "
)
),
".",
" "
)&"|"
),
" " ,
"|"
),
-1
)
)Excel solution 10 for Split Delimited Text into Columns, proposed by Duy Tùng:
=LET(
a,
A2:A20,
f,
LAMBDA(
x,
y,
TEXTSPLIT(
ARRAYTOTEXT(
x
),
y,
", ",
,
,
""
)
),
HSTACK(
f(
TEXTSPLIT(
a,
" :"
),
"."
),
f(
TEXTAFTER(
a,
": "
),
" "
)
)
)Excel solution 11 for Split Delimited Text into Columns, proposed by Sunny Baggu:
=REDUCE(
{"Level1",
"Level2",
"Level3",
"First Name",
"Last Name"},
A2:A20,
LAMBDA(
a,
v,
VSTACK(
a,
HSTACK(
TAKE(
TEXTSPLIT(
TEXTBEFORE(
v,
":"
) & REPT(
".",
2
),
"."
),
,
3
),
TEXTSPLIT(
TEXTAFTER(
v,
": "
),
" "
)
)
)
)
)Excel solution 12 for Split Delimited Text into Columns, proposed by Sunny Baggu:
=MAKEARRAY(
ROWS(
A2:A20
),
5,
LAMBDA(
r,
c,
INDEX(
HSTACK(
EXPAND(
TEXTSPLIT(
TEXTBEFORE(
INDEX(
A2:A20,
r,
),
" : "
),
"."
),
,
3,
""
),
TEXTSPLIT(
TEXTAFTER(
INDEX(
A2:A20,
r,
),
" : "
),
" "
)
),
c
)
)
)Excel solution 13 for Split Delimited Text into Columns, proposed by Abdallah Ally:
=DROP(
REDUCE(
"",
A2:A20,
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
EXPAND(
TEXTSPLIT(
TEXTBEFORE(
y,
" "
),
{"."}
),
,
3,
""
),
TEXTSPLIT(
TEXTAFTER(
y,
": "
),
" "
)
)
)
)
),
1
)Excel solution 14 for Split Delimited Text into Columns, proposed by Abdallah Ally:
=REDUCE(
{"Level1",
"Level2",
"Level3",
"First Name",
"Last Name"},
A2:A20,
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
EXPAND(
TEXTSPLIT(
TEXTBEFORE(
y,
" "
),
{"."}
),
,
3,
""
),
TEXTSPLIT(
TEXTAFTER(
y,
": "
),
" "
)
)
)
)
)Excel solution 15 for Split Delimited Text into Columns, proposed by Andy Heybruch:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
A2:A20,
LAMBDA(
_text,
TEXT(
TEXTBEFORE(
_text,
":"
),
"#.#.#"
)&
"."&
SUBSTITUTE(
TRIM(
TEXTAFTER(
_text,
":"
)
),
" ",
"."
)
)
)
),
".",
"|"
)Excel solution 16 for Split Delimited Text into Columns, proposed by Bilal Mahmoud kh.:
=LET(
a,
TAKE(
TEXTSPLIT(
TEXTJOIN(
"|",
,
A1:A19
),
":",
"|",
TRUE
),
,
1
),
b,
DROP(
TEXTSPLIT(
TEXTJOIN(
"|",
,
A1:A19
),
":",
"|",
TRUE
),
,
1
),
c,
IFERROR(
TEXTSPLIT(
TEXTJOIN(
"|",
,
a
),
".",
"|"
),
""
),
d,
HSTACK(
c,
b
),
d
)Excel solution 17 for Split Delimited Text into Columns, proposed by Milan Shrimali:
=let(
a,
RANGE,
b,
map(
a,
lambda(
x,
TEXTSPLIT(
TEXTBEFORE(
RANGE,
":"
),
"."
)
)
)c,
TEXTSPLIT(
TEXTAFTER(
RANGE,
":"
),
" "
),
hstack(
b,
c
)
)Excel solution 18 for Split Delimited Text into Columns, proposed by Tyler Cameron:
=DROP(
REDUCE(
"",
A2:A20,
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
EXPAND(
TEXTSPLIT(
TEXTBEFORE(
y,
" :"
),
"."
),
,
3,
""
),
TEXTSPLIT(
TEXTAFTER(
y,
": "
),
" "
)
)
)
)
),
1
)Excel solution 19 for Split Delimited Text into Columns, proposed by Ben Gutscher:
=LET(
split,
TEXTSPLIT(
A2,
{".",
":",
" "}
),
HSTACK(
INDEX(
split,
1
),
INDEX(
split,
2
),
INDEX(
split,
3
),
TAKE(
split,
,
-2
)
)
)Excel solution 20 for Split Delimited Text into Columns, proposed by Brad Dixon:
=LET(
_baseData,
A2:A20,
_concatenatedIndexLevels,
TRIM(
TEXTBEFORE(
_baseData,
":"
)
),
_names,
TRIM(
TEXTAFTER(
_baseData,
":"
)
),
_maxLevels,
MAX(
MAP(
_concatenatedIndexLevels,
LAMBDA(
row,
COLUMNS(
TEXTSPLIT(
row,
"."
)
)
)
)
),
_levelsBreakout,
MAKEARRAY(
ROWS(
_baseData
),
_maxLevels,
LAMBDA(
r,
c,
MAP(
INDEX(
_concatenatedIndexLevels,
r,
1
),
LAMBDA(
row,
IFERROR(
INDEX(
TEXTSPLIT(
row,
"."
),
1,
c
),
""
)
)
)
)
),
_namesBreakout,
MAKEARRAY(
ROWS(
_baseData
),
2,
LAMBDA(
r,
c,
MAP(
INDEX(
_names,
r,
1
),
LAMBDA(
row,
INDEX(
TEXTSPLIT(
row,
" "
),
1,
c
)
)
)
)
),
_levelsHeadings,
MAKEARRAY(
1,
_maxLevels,
LAMBDA(
r,
c,
"Level" & c
)
),
_result,
HSTACK(
VSTACK(
_levelsHeadings,
_levelsBreakout
),
VSTACK(
{"First Name",
"Last Name"},
_namesBreakout
)
),
_result
)Solving the challenge of Split Delimited Text into Columns with Python
Python solution 1 for Split Delimited Text into Columns, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("433 Text Split.xlsx", usecols="A", nrows=20)
test = pd.read_excel("433 Text Split.xlsx", usecols="C:G", nrows=20)
input[["numbers", "names"]] = input["Text"].str.split(" : ", expand=True)
input[["Level1","Level2", "Level3"]] = input["numbers"].str.split(".", expand=True).apply(pd.to_numeric)
input = input.drop(columns=["Text", "numbers", "names"])
print(input.equals(test))
Solving the challenge of Split Delimited Text into Columns with Python in Excel
Python in Excel solution 1 for Split Delimited Text into Columns, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_433 - Text Split.xlsx'
df = pd.read_excel(file_path, usecols='A')
# Perform data transformation and cleansing
def text_split(col):
levels = col[ : col.find(' ')].split('.')
levels = levels + [''] * (3 - len(levels))
names = col.split(' ')[-2: ]
return le&vels + names
df[columns] = df['Text'].apply(text_split).tolist()
print(f'nMy Resultsn{df}')
Solving the challenge of Split Delimited Text into Columns with R
R solution 1 for Split Delimited Text into Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(unglue)
input = read_excel("Excel/433 Text Split.xlsx", range = "A1:A20")
test = read_excel("Excel/433 Text Split.xlsx", range = "C1:G20")
result = input %>%
unglue_unnest(Text, patterns = patterns) %>%
identical(result, test)
# [1] TRUE
R solution 2 for Split Delimited Text into Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/433 Text Split.xlsx", range = "A1:20")
test = read_excel("Excel/433 Text Split.xlsx", range = "C1:G20")
pattern = "(\d+)(\.\d+)?(\.\d+)?\s*:\s*(\w+)\s+(\w+)"
result = str_match(input$Text, pattern) %>%
as_tibble() %>%
select(-c(1)) %>%
mutate(across(c("V2", "V3", "V4"), ~ str_replace(.x, pattern = "[:punct:]", replacement = ""))) %>%
import pandas as pd
import re
input = pd.read_excel("433 Text Split.xlsx", usecols="A", nrows=20)
test = pd.read_excel("433 Text Split.xlsx", usecols="C:G", nrows=20)
pattern = "(\d+)(\.\d+)?(\.\d+)?\s*:\s*(\w+)\s+(\w+)"
input[["Level2", "Level3"]] = input[["Level2", "Level3"]].replace({".": ""}, regex=True).apply(pd.to_numeric)
input[["Level1"]] = input[["Level1"]].astype("int64")
input = input.drop(columns=["Text"])
R solution 3 for Split Delimited Text into Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/433 Text Split.xlsx", range = "A1:B20")
test = read_excel("Excel/433 Text Split.xlsx", range = "C1:G20")
result = input %>%
separate(Text, into = c("Levels", "Names"), sep = " : ") %>%
separate(Levels, into = c("Level1", "Level2", "Level3"), sep = "\.") %>%
select(-c(...2))
R solution 4 for Split Delimited Text into Columns, proposed by Anil Kumar Goyal:
library(tidyr)
library(readxl)
df <- read_excel("Excel/Excel_Challenge_433 - Text Split.xlsx", range = cell_cols(LETTERS[1]))
df %>%
separate(Text, into = c("Levels", "Names"), sep = " : ") %>%
separate(Levels, into = paste0("Level", 1:3), sep = "\.", fill = "right") %>%
&