(Excel formulas also welcome) Extract all alphabets in one column and numbers in another column. For PQ (not for Excel formulas) – Make sure that query is dynamic i.e. if number of rows and columns increase/decrease, the query should give right result.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 2
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Separate Alphabets and Numbers with Power Query
Power Query solution 1 for Separate Alphabets and Numbers, proposed by 🇰🇷 Taeyong Shin:
let
Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attr", "Value"),
Result = Table.FromColumns(
{List.Select(Unpivot[Value], each _ is text), List.Select(Unpivot[Value], each _ is number)},
type table [Alpha = text, Number = number]
)
in
ResultPower Query solution 2 for Separate Alphabets and Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
AllLists = List.Distinct(List.Combine(Table.ToRows(Source))),
Alphabets = List.Select(AllLists, (f) => f is text),
Numbers = List.Select(AllLists, (f) => f is number),
Records = List.Transform(
List.Zip({Alphabets, Numbers}),
(f) => Record.FromList(f, {"Alphabets", "Numbers"})
),
Return = Table.FromRecords(Records)
in
ReturnPower Query solution 3 for Separate Alphabets and Numbers, proposed by Luan Rodrigues:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
TipoAlterado = Table.TransformColumnTypes(
Fonte,
{{"Data1", type any}, {"Data2", type text}, {"Data3", type any}, {"Data4", type any}}
),
Col_Mesc = Table.AddColumn(
TipoAlterado,
"Mesclado",
each Text.Combine(
{
Text.From([Data1], "pt-BR"),
[Data2],
Text.From([Data3], "pt-BR"),
Text.From([Data4], "pt-BR")
},
"-"
),
type text
),
Select = Table.AddColumn(
Col_Mesc,
"Personalizar",
each Text.Select([Mesclado], {"-", "0" .. "9"})
),
Col_Remov = Table.SelectColumns(Select, {"Personalizar"}),
Pad = Table.AddColumn(Col_Remov, "Personalizar.1", each Text.PadStart([Personalizar], 4, "-"))[
[Personalizar.1]
],
Dividir_Col = Table.SplitColumn(
Pad,
"Personalizar.1",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{
"Personalizar.1.1",
"Personalizar.1.2",
"Personalizar.1.3",
"Personalizar.1.4",
"Personalizar.1.5"
}
),
Vlr_Subst = Table.ReplaceValue(
Dividir_Col,
"-",
"",
Replacer.ReplaceText,
{"Personalizar.1.1", "Personalizar.1.2", "Personalizar.1.3", "Personalizar.1.4"}
),
Col_NDinam = Table.UnpivotOtherColumns(Vlr_Subst, {}, "Atributo", "Valor")[[Valor]],
LinhasFiltradas = Table.SelectRows(Col_NDinam, each ([Valor] <> ""))
in
LinhasFiltradasPower Query solution 4 for Separate Alphabets and Numbers, proposed by Luan Rodrigues:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
TipoAlterado = Table.TransformColumnTypes(
Fonte,
{{"Data1", type any}, {"Data2", type text}, {"Data3", type any}, {"Data4", type any}}
),
Col_Mesc = Table.AddColumn(
TipoAlterado,
"Mesclado",
each Text.Combine(
{
Text.From([Data1], "pt-BR"),
[Data2],
Text.From([Data3], "pt-BR"),
Text.From([Data4], "pt-BR")
},
""
),
type text
),
Select = Table.AddColumn(Col_Mesc, "Personalizar", each Text.Select([Mesclado], {"A" .. "Z"})),
Col_Remov = Table.SelectColumns(Select, {"Personalizar"}),
Pad = Table.AddColumn(Col_Remov, "Personalizar.1", each Text.PadStart([Personalizar], 4, "-"))[
[Personalizar.1]
],
Dividir_Col = Table.SplitColumn(
Pad,
"Personalizar.1",
Splitter.SplitTextByRepeatedLengths(1),
{"Personalizar.1.1", "Personalizar.1.2", "Personalizar.1.3", "Personalizar.1.4"}
),
Vlr_Subst = Table.ReplaceValue(
Dividir_Col,
"-",
"",
Replacer.ReplaceText,
{"Personalizar.1.1", "Personalizar.1.2", "Personalizar.1.3", "Personalizar.1.4"}
),
Col_NDinam = Table.UnpivotOtherColumns(Vlr_Subst, {}, "Atributo", "Valor")[[Valor]],
LinhasFiltradas = Table.SelectRows(Col_NDinam, each ([Valor] <> ""))
in
LinhasFiltradasPower Query solution 5 for Separate Alphabets and Numbers, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(
#"Unpivoted Columns",
"Custom",
each if Value.Is([Value], type number) then "Number" else "Alpha"
),
#"Grouped Rows" = Table.Group(
#"Added Custom",
{"Custom"},
{{"Help", each _, type table [Attribute = text, Value = any, Custom = text]}}
),
#"Added Custom1" = Table.AddColumn(
#"Grouped Rows",
"Custom.1",
each Table.AddIndexColumn([Help], "I")
),
#"Expanded Custom.1" = Table.ExpandTableColumn(
#"Added Custom1",
"Custom.1",
{"Value", "I"},
{"Value", "I"}
),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1", {"Help"}),
#"Pivoted Column" = Table.Pivot(
#"Removed Columns",
List.Distinct(#"Removed Columns"[Custom]),
"Custom",
"Value"
),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column", {"I"})
in
#"Removed Columns1"Power Query solution 6 for Separate Alphabets and Numbers, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Alphabets", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type", {{"Alphabets", Order.Ascending}}),
#"Grouped Rows" = Table.Group(
#"Sorted Rows",
{"Alphabets"},
{{"All", each _, type table [Alphabets = nullable text]}}
),
#"Added Custom" = Table.AddColumn(
#"Grouped Rows",
"Custom",
each Table.AddIndexColumn([All], "Index", 1, 1)
),
#"Expanded Custom" = Table.ExpandTableColumn(
#"Added Custom",
"Custom",
{"Alphabets", "Index"},
{"Alphabets.1", "Index"}
),
#"Removed Other Columns" = Table.SelectColumns(
#"Expanded Custom",
{"Alphabets", "Alphabets.1", "Index"}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Removed Other Columns",
{{"Alphabets.1", type text}, {"Index", Int64.Type}}
),
#"Pivoted Column" = Table.Pivot(
#"Changed Type1",
List.Distinct(#"Changed Type1"[Alphabets.1]),
"Alphabets.1",
"Alphabets"
),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column", {"Index"})
in
#"Removed Columns"Power Query solution 7 for Separate Alphabets and Numbers, proposed by Antriksh Sharma:
let
Source = OriginalData,
InsertTextList = Table.AddColumn(
Source,
"TextOnly",
each List.Select(Record.ToList(_), each Value.Is(Value.FromText(_), type text))
),
InsertNumbersList = Table.AddColumn(
InsertTextList,
"NumbersOnly",
each List.Select(Record.ToList(_), each Value.Is(Value.FromText(_), type number))
),
NumbersOnlyList = Table.SelectRows(
Table.ExpandListColumn(Table.SelectColumns(InsertNumbersList, {"NumbersOnly"}), "NumbersOnly"),
each [NumbersOnly] <> null and [NumbersOnly] <> ""
)[NumbersOnly],
TextOnlyList = Table.SelectRows(
Table.ExpandListColumn(Table.SelectColumns(InsertNumbersList, {"TextOnly"}), "TextOnly"),
each [TextOnly] <> null and [TextOnly] <> ""
)[TextOnly],
Result = Table.FromColumns({TextOnlyList, NumbersOnlyList}),
ChangedTypeFinal = Table.TransformColumnTypes(
Result,
{{"Column1", type text}, {"Column2", Int64.Type}}
),
RenamedColumns = Table.RenameColumns(
ChangedTypeFinal,
{{"Column1", "Alpha"}, {"Column2", "Number"}}
)
in
RenamedColumnsPower Query solution 8 for Separate Alphabets and Numbers, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Table.FromColumns({List.Combine(Table.ToRows(Source))}),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each [Column1] is number),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each _[Column1]}})[Count],
Custom2 = Table.FromColumns(#"Grouped Rows", {"Alpha", "Number"})
in
Custom2Power Query solution 9 for Separate Alphabets and Numbers, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Sample"]}[Content],
ValueList = List.Combine(List.Transform(Table.ToRecords(Source), Record.FieldValues)),
t = Table.FromColumns(
{List.Select(ValueList, each _ is text), List.Select(ValueList, each _ is number)},
type table [Alpha = Text.Type, Number = Int8.Type]
)
in
tPower Query solution 10 for Separate Alphabets and Numbers, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Sample"]}[Content],
ValueList = List.Combine(Table.ToColumns(Source)),
t = Table.FromColumns(
{List.Select(ValueList, each _ is text), List.Select(ValueList, each _ is number)},
type table [Alpha = Text.Type, Number = Int8.Type]
)
in
tPower Query solution 11 for Separate Alphabets and Numbers, proposed by Udit Chatterjee:
let
Source = #"PQChallenge-02",
unpivotOtherCols = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
keepReqColOnly = Table.RemoveColumns(unpivotOtherCols, {"Attribute"}),
// convert table to a list
mainList = Table.Column(keepReqColOnly, "Value"),
// get list of aphas and list of numbers
listAlphas = List.RemoveMatchingItems(
List.Transform(mainList, each Text.Select(_, {"A" .. "Z", "a" .. "z"})),
{""}
),
listNums = List.RemoveMatchingItems(
List.Transform(mainList, each Text.Select(_, {"0" .. "9"})),
{""}
),
// combine lists to create a table
mainTable = Table.FromColumns({listAlphas, listNums}, {"Alpha", "Number"}),
datatypeDetect = Table.TransformColumnTypes(
mainTable,
{{"Alpha", type text}, {"Number", Int64.Type}}
)
in
datatypeDetectPower Query solution 12 for Separate Alphabets and Numbers, proposed by Kamlesh Mohite:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Data1", type text}, {"Data3", type text}, {"Data4", type text}}, "en-GB"),{"Data1", "Data2", "Data3", "Data4"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Duplicated Column" = Table.DuplicateColumn(#"Split Column by Delimiter", "Merged", "Merged - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Merged - Copy", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Merged - Copy", null}}),
#"Added Custom" = Table.AddColumn(#"Replaced Errors", "Custom", each if Value.Is([#"Merged - Copy"], type number)
then "Number"
else "Alpha"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Merged - Copy"}),
Power Query solution 13 for Separate Alphabets and Numbers, proposed by Oleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name = "input"]}[Content],
from = List.Buffer(List.Combine(Table.ToRows(src))),
to = [
a = List.Select(from, (x) => Value.Is(x, Text.Type)),
b = List.Select(from, (x) => not Value.Is(x, Text.Type)),
c = Table.FromRows(List.Zip({a, b}), {"Alpha", "Number"})
][c]
in
toSolving the challenge of Separate Alphabets and Numbers with Excel
Excel solution 1 for Separate Alphabets and Numbers, proposed by Rick Rothstein:
=TRIM(
MID(
SUBSTITUTE(
" "&TEXTJOIN(
" ",
,
IF(
ISNUMBER(
-A2:D10
),
"",
A2:D10
)
),
" ",
REPT(
" ",
300
)
),
SEQUENCE(
36
)*300,
300
)
)
and this formula in cell G2...
=TRIM(
MID(
SUBSTITUTE(
" "&TEXTJOIN(
" ",
,
IF(
ISNUMBER(
-A2:D10
),
A2:D10,
""
)
),
" ",
REPT(
" ",
300
)
),
SEQUENCE(
36
)*300,
300
)
)Excel solution 2 for Separate Alphabets and Numbers, proposed by محمد حلمي:
=LET(
b,
B3:E11,
IFNA(
HSTACK(
TOCOL(
IF(
ISTEXT(
b
),
b,
NA()
),
2
),
TOCOL(
--b,
2
)
),
""
)
)Excel solution 3 for Separate Alphabets and Numbers, proposed by محمد حلمي:
=IFERROR(LET(N;
INDEX($J$3:$M$11;
MOD(
ROW(
1:36
)-1;
9
)+1;
INT((ROW(
1:36
)-1)/9)+1);
IF(
COLUMN(
A1:B1
)=1;
FILTER(
N;
IF(
ISTEXT(
N
);
1
)
);
FILTER(
N;
IF(
ISNUMBER(
N
);
1
)
)
));
"")Excel solution 4 for Separate Alphabets and Numbers, proposed by محمد حلمي:
Jardiel Euflázio
CHOOSE({12},
Excel solution 5 for Separate Alphabets and Numbers, proposed by Duy Tùng:
=LET(
a,
B3:E11,
IFNA(
HSTACK(
TOCOL(
IFS(
ISTEXT(
a
),
a
),
3
),
TOCOL(
a/ISNUMBER(
a
),
3
)
),
""
)
)Excel solution 6 for Separate Alphabets and Numbers, proposed by Bhavya Gupta:
=LET(
a,
TOCOL(
B3:E11
&),
IFNA(
HSTACK(
FILTER(
a,
ISTEXT(
a
)
),
FILTER(
a,
ISNUMBER(
a
)
)
),
""
)
)Excel solution 7 for Separate Alphabets and Numbers, proposed by Antriksh Sharma:
=LET(
a,
B3:E11,
b,
TOCOL(
a
),
alpha,
FILTER(
b,
b > ""
),
nums,
EXPAND(
FILTER(
b,
b < ""
),
ROWS(
alpha
),
,
""
),
VSTACK(
{"Alpha",
"Number"},
HSTACK(
alpha,
nums
)
)
)Excel solution 8 for Separate Alphabets and Numbers, proposed by Jardiel Euflázio:
=TEXTSPLIT(
TEXTJOIN(
"-",
,
IF(
ISTEXT(
B3:E11
),
B3:E11,
""
)
),
,
"-"
)
or
=FILTER(
TEXTSPLIT(
TEXTJOIN(
"-",
,
B3:E11
),
,
"-"
),
ISERROR(
0+TEXTSPLIT(
TEXTJOIN(
"-",
,
B3:E11
),
,
"-"
)
)
)
For numbers:
=TEXTSPLIT(
TEXTJOIN(
"-",
,
IF(
ISNUMBER(
B3:E11
),
B3:E11,
""
)
),
,
"-"
)+0
or
=FILTER(
TEXTSPLIT(
TEXTJOIN(
"-",
,
B3:E11
),
,
"-"
),
ISNUMBER(
0+TEXTSPLIT(
TEXTJOIN(
"-",
,
B3:E11
),
,
"-"
)
)
)
Single formula for two columns:
=IFERROR(
CHOOSE(
{12},
TEXTSPLIT(
TEXTJOIN(
"-",
,
IF(
ISTEXT(
B3:E11
),
B3:E11,
""
)
),
,
"-"
),
TEXTSPLIT(
TEXTJOIN(
"-",
,
IF(
ISNUMBER(
B3:E11
),
B3:E11,
""
)
),
,
"-"
)+0
),
""
)Excel solution 9 for Separate Alphabets and Numbers, proposed by Sergei Baklan:
=IFNA(
HSTACK(
TOCOL(
IF(
ISNUMBER(
data
),
NA(),
data
),
3
),
TOCOL(
IF(
ISTEXT(
data
),
NA(),
data
),
3
)
),
""
)Excel solution 10 for Separate Alphabets and Numbers, proposed by Maciej Kopczyński:
=LET(
dataToColumn,
TOCOL(
B3:E11
),
strColumn,
FILTER(
dataToColumn,
ISTEXT(
dataToColumn
)
),
digitColumn,
FILTER(
dataToColumn,
ISNUMBER(
dataToColumn
)
),
result,
IFERROR(
VSTACK(
{"Alpha",
"Number"},
HSTACK(
strColumn,
digitColumn
)
),
""
),
result
)Excel solution 11 for Separate Alphabets and Numbers, proposed by Amardeep Singh:
=LET(
data,
TOCOL(
B3:E11,
3
),
alpha,
FILTER(
data,
ISTEXT(
data
),
"none"
),
num,
FILTER(
data,
ISNUMBER(
data
),
"none"
),
IFNA(
VSTACK(
{"Alpha",
"Numbers"},
HSTACK(
alpha,
num
)
),
""
)
)Excel solution 12 for Separate Alphabets and Numbers, proposed by Yasir Ali Khan:
=ArrayFormula(LET(a,
FLATTEN(
SPLIT(
TEXTJOIN(
",",
TRUE,
A3:D11
),
","
)
),
(FILTER(
a,
ISTEXT(
a
)
))))
For Numbers
=ArrayFormula(LET(a,
FLATTEN(
SPLIT(
TEXTJOIN(
",",
TRUE,
A3:D11
),
","
)
),
(FILTER(
a,
ISNUMBER(
a
)
))))Excel solution 13 for Separate Alphabets and Numbers, proposed by Steven Morath:
=IFERROR(
HSTACK(
VSTACK(
"Alpha",
FILTER(
TOCOL(
array2
),
ISTEXT(
TOCOL(
array2
)
)
)
),
VSTACK(
"Number",
FILTER(
TOCOL(
array2
),
ISNUMBER(
TOCOL(
array2
)
)
)
)
),
""
)Solving the challenge of Separate Alphabets and Numbers with Python in Excel
Python in Excel solution 1 for Separate Alphabets and Numbers, proposed by Antriksh Sharma:
df = xl("B2:E11", headers=True)
v = df.to_numpy().flatten().tolist()
alpha = [a for a in v if isinstance(a, str)]
number = [i for i in v if isinstance(i, (int, float))]
pd.DataFrame(
{
'Alpha': pd.Series(alpha, dtype = str),
'Number': pd.Series(number, dtype = int)
}
).fillna('')