Split the ID into two columns: The first column should contain all vowels (A, E, I, O, U). The second column should contain all consonants and numbers.
📌 Challenge Details and Links
Challenge Number: 186
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Column Splitting! Part 4 with Power Query
Power Query solution 1 for Column Splitting! Part 4, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
V = {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"},
_ = Table.SplitColumn(Source, "ID", each {Text.Select(_, V), Text.Remove(_, V)}, 2)
in
_Power Query solution 2 for Column Splitting! Part 4, proposed by Brian Julius:
let
Source = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name = "rng"]}[Content]),
Process = Table.AddColumn(
Source,
"Custom",
each [
Vowels = {"A", "E", "I", "O", "U", "a", "e", "i", "i"},
a = Text.ToList([ID]),
b = List.Intersect({Vowels, a}),
c = List.RemoveMatchingItems(a, b),
d = try Text.Combine(b) otherwise null,
e = try Text.Combine(c) otherwise null,
f = d & "," & e
][f]
),
Split = Table.SplitColumn(
Process,
"Custom",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"ID.1", "ID.2"}
)
in
SplitPower Query solution 3 for Column Splitting! Part 4, proposed by Luan Rodrigues:
let
Fonte = Table.TransformRows(
Tabela1,
each [
a = Text.Select([ID], {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"}),
b = try Text.AfterDelimiter([ID], a) otherwise [ID],
c = Table.FromRows({{a, b}}, List.Transform({1 .. 2}, (x) => "ID." & Text.From(x)))
][c]
),
res = Table.Combine(Fonte)
in
resPower Query solution 4 for Column Splitting! Part 4, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Fx = (x)=> let
a = Text.ToList(x),
b = List.Select(a, each List.ContainsAny({_},{"A","E","I","O","U","a","e","i","o","u"})),
c = List.Difference(a,b),
d = Table.FromRows({{Text.Combine(b)}&{Text.Combine(c)}},{"ID.1","ID.2"})
in d,
e = Table.AddColumn(S,"A", each Fx([ID]))[[A]],
Sol = Table.ExpandTableColumn(e,"A",{"ID.1","ID.2"})
in
SolPower Query solution 5 for Column Splitting! Part 4, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Combine(Table.AddColumn(Source, "A", each
let
a = {"A","E","I","O","U"},
b = List.Transform(a, Text.Lower),
c = Text.Select([ID], a&b),
d = Text.Remove([ID], a&b),
e = Table.FromRows({{c,d}},{"ID.1","ID.2"})
in e)[A])
in
SolPower Query solution 6 for Column Splitting! Part 4, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = {"A", "I", "E", "O", "U", "a", "i", "e", "o", "u"},
S = List.Transform(Source[ID], each Text.Select(_, L)),
R = List.Transform(Source[ID], each Text.Remove(_, L)),
Result = Table.FromColumns({S, R}, {"ID.1", "ID.2"})
in
ResultPower Query solution 7 for Column Splitting! Part 4, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumns(
A,
{},
each [ID1 = Text.Select(_, Text.ToList("aeiouAEIOU")), ID2 = List.Last(Text.Split(_, ID1))]
),
C = Table.ExpandRecordColumn(B, "ID", {"ID1", "ID2"})
in
CPower Query solution 8 for Column Splitting! Part 4, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Data",
each [
a = Text.Remove([ID], Text.ToList("aAeEiIoOuU")),
b = Text.Remove([ID], Text.ToList(a)),
c = [ID.1 = b, ID.2 = a]
][c]
)[[Data]],
Result = Table.ExpandRecordColumn(AddCol, "Data", {"ID.1", "ID.2"})
in
ResultPower Query solution 9 for Column Splitting! Part 4, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Vowels = Text.ToList("AaEeIiOoUu"),
Split = Table.SplitColumn(Source, "ID", each {Text.Select(_, Vowels), Text.Remove(_, Vowels)})
in
SplitPower Query solution 10 for Column Splitting! Part 4, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Vowels = {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"},
ID1 = Table.AddColumn(
Source,
"ID.1",
each Text.Combine(List.Intersect({Text.ToList([ID]), Vowels}))
),
ID2 = Table.AddColumn(
ID1,
"ID.2",
each Text.Combine(List.RemoveMatchingItems(Text.ToList([ID]), Vowels))
),
Result = Table.RemoveColumns(ID2, {"ID"})
in
ResultPower Query solution 11 for Column Splitting! Part 4, proposed by Aleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name = "Source"]}[Content],
lst = List.Buffer({"a", "o", "u", "i", "e", "A", "O", "U", "I", "E"}),
res = Table.SplitColumn(
src,
"Column1",
(r) => {Text.Select(r, lst), Text.Remove(r, lst)},
{"ID.1", "ID.2"}
)
in
resPower Query solution 12 for Column Splitting! Part 4, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
P = "AEIOU",
L = Table.ToList (H, each let x = List.RemoveItems(Text.SplitAny(_{0},P & Text.Lower(P)),{""}){0} ? ?? "" in {Text.Replace(_{0},x,""), x}),
C = Table.FromRows(L, {"ID.1", "ID.2"})
in CPower Query solution 13 for Column Splitting! Part 4, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
List = {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"},
Custom = Table.AddColumn(
Source,
"ID.2",
each [
ID.2 = Text.Combine(
List.Select(Splitter.SplitTextByAnyDelimiter(List)([ID]), each _ <> ""),
""
),
ID.1 = Text.Replace([ID], ID.2, "")
]
),
Final = Table.RemoveColumns(Table.ExpandRecordColumn(Custom, "ID.2", {"ID.1", "ID.2"}), {"ID"})
in
FinalSolving the challenge of Column Splitting! Part 4 with Excel
Excel solution 1 for Column Splitting! Part 4, proposed by 🇰🇷 Taeyong Shin:
=REGEXEXTRACT(
B3:B8,
{"[aeiou]+",
"[^aeiou]+"}&"|$",
,
1
)Excel solution 2 for Column Splitting! Part 4, proposed by 🇵🇪 Ned Navarrete C.:
=REGEXREPLACE(
B3:B8,
{"[^aeiou]",
"[aeiou]"},
"",
,
1
)Excel solution 3 for Column Splitting! Part 4, proposed by Oscar Mendez Roca Farell:
=REGEXEXTRACT(
B3:B8,
{"[AaEeIiOoUu]*",
"[^AaEeIiOoUu]w*"}&"|$"
)Excel solution 4 for Column Splitting! Part 4, proposed by Kris Jaganah:
=LET(
a,
B3:B8,
b,
IFERROR(
TEXTSPLIT(
a,
{"a",
"e",
"i",
"o",
"u"},
,
1,
1
),
""
),
HSTACK(
TEXTSPLIT(
a,
IF(
b="",
" ",
b
)
),
b
)
)Excel solution 5 for Column Splitting! Part 4, proposed by Ivan William:
=REDUCE(
{"ID.1",
"IN.2"},
B3:B8,
LAMBDA(
x,
y,
LET(
c,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
CONCAT(
FILTER(
c,
BYROW(
SEARCH(
c,
{"a",
"e",
"i",
"o",
"u"}
),
COUNT
),
""
)
),
VSTACK(
x,
HSTACK(
b,
SUBSTITUTE(
y,
b,
)
)
)
)
)
)Excel solution 6 for Column Splitting! Part 4, proposed by Sunny Baggu:
=LET( _a,
MAP( B3:B8, LAMBDA(
a,
IFERROR(
TEXTSPLIT(
a,
,
{"a"; "e"; "i"; "o"; "u"},
1,
1
),
""
)
) ), _b,
MAP( _a, B3:B8, LAMBDA(
x,
y,
IF(
x = "",
y,
IFERROR(
TEXTSPLIT(
y,
,
x,
1
),
""
)
)
) ), HSTACK(
_b,
_a
))Excel solution 7 for Column Splitting! Part 4, proposed by Alejandro Campos:
=LET( rng,
B3:B8, R,
REGEXEXTRACT, IFNA(
HSTACK(
R(
rng,
"[aeiou]+",
,
1
),
R(
rng,
"[^aeiou]+",
,
1
)
),
""
)
)Excel solution 8 for Column Splitting! Part 4, proposed by Asheesh Pahwa:
=LET(
m,
MAP(
B3:B8,
LAMBDA(
x,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
I,
ISNUMBER(
XMATCH(
m,
{"a",
"e",
"i",
"o",
"u"}
)
),
CONCAT(
FILTER(
m,
I,
""
)
)
)
)
),
I,
IFERROR(
MAP(
B3:B8,
LAMBDA(
x,
TEXTSPLIT(
x,
G2:K2,
,
1,
1
)
)
),
""
),
HSTACK(
m,
I
)
)Excel solution 9 for Column Splitting! Part 4, proposed by CA Raghunath Gundi:
=LET(
vow,
{"a",
"e",
"i",
"o",
"u"}, ID1,
BYROW(
B3:B8,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
),
CONCAT(
FILTER(
m,
ISNUMBER(
XMATCH(
m,
vow
)
),
""
)
)
)
)
), ID2,
BYROW(
B3:B8,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
),
CONCAT(
FILTER(
m,
ISERROR(
XMATCH(
m,
vow
)
),
""
)
)
)
)
), HSTACK(
ID1,
ID2
)
)Excel solution 10 for Column Splitting! Part 4, proposed by Eddy Wijaya:
=REDUCE(
D2:E2,
B3:B8,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
c,
{"A",
"E",
"I",
"O",
"U"},
sp,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
f,
CONCAT(
FILTER(
sp,
ISNUMBER(
MATCH(
sp,
c,
0
)
),
""
)
),
r,
HSTACK(
f,
SUBSTITUTE(
v,
f,
""
)
),
IFERROR(
--r,
r
)
)
)
)
)Excel solution 11 for Column Splitting! Part 4, proposed by ferhat CK:
=REDUCE(
{"ID.1",
"ID.2"},
B3:B8,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
REGEXEXTRACT(
y,
".",
1
),
b,
XMATCH(
a,
{"A",
"E",
"I",
"O",
"U",
"a",
"e",
"i",
"o",
"u"}
),
HSTACK(
CONCAT(
IF(
ISNUMBER(
b
),
a,
""
)
),
CONCAT(
IF(
ISERROR(
b
),
a,
""
)
)
)
)
)
)
)Excel solution 12 for Column Splitting! Part 4, proposed by Gabriel Pugliese:
=HSTACK(
REGEXREPLACE(
B3:B8,
"[^aeiou]",
"",
0,
1
),
REGEXREPLACE(
B3:B8, "[aeiou]",
"",
0,
1
)
)Excel solution 13 for Column Splitting! Part 4, proposed by Hamidi Hamid:
=LET(
f,
CODE(
{"A",
"E",
"I",
"O",
"U",
"a",
"e",
"i",
"o",
"u"}
),
x,
BYROW(
IFERROR(
CHAR(
XLOOKUP(
IFERROR(
CODE(
MID(
B3:B8,
SEQUENCE(
,
20
),
1
)
),
""
),
f,
f,
""
)
),
""
),
CONCAT
),
y,
MAP(
B3:B8,
x,
LAMBDA(
a,
b,
IFERROR(
TEXTJOIN(
"",
,
TEXTSPLIT(
a,
b,
)
),
""
)
)
),
HSTACK(
x,
IF(
x="",
B3:B8,
y
)
)
)Excel solution 14 for Column Splitting! Part 4, proposed by Hamidi Hamid:
=LET(
v,
MID(
B3:B8,
SEQUENCE(
,
11
),
1
),
x,
IFERROR(
XLOOKUP(
CODE(
v
),
CODE(
{"A",
"E",
"U",
"I",
"O",
"a",
"e",
"u",
"i",
"o"}
),
CODE(
{"A",
"E",
"U",
"I",
"O",
"a",
"e",
"u",
"i",
"o"}
)
),
""
),
w,
IF(
x="",
"",
CHAR(
x
)
),
r,
BYROW(
w,
CONCAT
),
n,
BYROW(
IF(
w="",
v,
""
),
CONCAT
),
HSTACK(
r,
n
)
)Excel solution 15 for Column Splitting! Part 4, proposed by Hussein SATOUR:
=LET(
I,
B3:B8,
a,
IFNA(
REGEXEXTRACT(
I,
"[aeiou]+",
1,
1
),
""
),
HSTACK(
a,
SUBSTITUTE(
I,
a,
""
)
)
)Excel solution 16 for Column Splitting! Part 4, proposed by Pieter de B.:
=TEXTSPLIT(
TEXTAFTER(
"|"&MAP(
B3:B8,
LAMBDA(
b,
LET(
x,
{"a",
"e",
"i",
"o",
"u"},
c,
TEXTSPLIT(
b,
HSTACK(
x,
UPPER(
x
)
),
,
1
),
IFERROR(
CONCAT(
TEXTSPLIT(
b,
c
)
)&"|"&CONCAT(
c
),
b&"||"
)
)
)
),
"|",
{1,
2}
),
"|"
)Excel solution 17 for Column Splitting! Part 4, proposed by Tomasz Jakóbczyk:
=LET(
id,
B3:B8,
HSTACK(
REGEXREPLACE(
id,
"[^aeoiu]",
"",
,
1
),
REGEXREPLACE(
id,
"[aeoiu]",
"",
,
1
)
)
)Solving the challenge of Column Splitting! Part 4 with Python
Python solution 1 for Column Splitting! Part 4, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "CH-186 Column Splitting.xlsx"
input = pd.read_excel(path, usecols="B", skiprows=1, nrows=7)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=7).fillna("").astype('str')
input['ID.1'] = input['ID'].str.replace(r'[^aeiouAEIOU]', '', regex=True)
input['ID.2'] = input['ID'].str.replace(r'[aeiouAEIOU]', '', regex=True)
result = input.drop(columns=['ID'])
print(result.equals(test)) # TruePython solution 2 for Column Splitting! Part 4, proposed by Luan Rodrigues:
import pandas as pd
file = "CH-186 Column Splitting.xlsx"
df = pd.read_excel(file, usecols="B",skiprows=1)
df['ID.1'] = df['ID'].str.replace(r'[^aeiouAEIOU]','',regex=True)
df['ID.2'] = df['ID'].str.replace(r'[aeiouAEIOU]', '', regex=True)
del df['ID']
print(df)Python solution 3 for Column Splitting! Part 4, proposed by Abdallah Ally:
import pandas as pd
import re
# Load the Excel file
file_path = 'CH-186 Column Splitting.xlsx'
df = pd.read_excel(io=file_path, usecols='B', skiprows=1)
# Perform data transformation
df['ID.1'] = df.ID.map(lambda x: ''.join(re.findall('[aeiou]', x, flags=re.I)))
df['ID.2'] = df.ID.map(lambda x: ''.join(re.findall('[^aeiou]', x, flags=re.I)))
df = df[['ID.1', 'ID.2']]
# Display the final results
dfSolving the challenge of Column Splitting! Part 4 with Python in Excel
Python in Excel solution 1 for Column Splitting! Part 4, proposed by Alejandro Campos:
df = pd.DataFrame([[v:=''.join(c for c in s if c in "AEIOUaeiou"), ''.join(
c for c in s if c not in v)] for s in xl("B3:B8")[0]], columns=['ID.1', 'ID.2'])Solving the challenge of Column Splitting! Part 4 with R
R solution 1 for Column Splitting! Part 4, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-186 Column Splitting.xlsx"
input = read_excel(path, range = "B2:B8")
test = read_excel(path, range = "D2:E8") %>%
mutate_all(~replace_na(., ""))
result = input %>%
mutate(ID.1 = str_remove_all(ID, "[^aeiouAEIOU]"),
ID.2 = str_remove_all(ID, "[aeiouAEIOU]")) %>%
select(-ID)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE