Forum Discussion
Solved
Extract numbers from a string column in excel using power query
Hi Community, Can someone please help? How can I extract the numbers from the string in Excel or CSV files using Power Query. Like in the example below: Thanks for your help in ...
Splitter.SplitTextByWhitespace()(Text.Trim(Text.Select(your_text, {" ", "0".."9"})))use this formula (I have split it into several steps to be more clear)
let Source = Table.FromColumns({{"This is a 500 of 600", "800 of 558 available", "order has 60 of 20"}},{"Description"}), #"Added Custom" = Table.AddColumn(Source, "S1", each Text.Split([Description]," ")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "S2", each List.Transform([S1], each try Number.From(_) otherwise null)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "S3", each List.RemoveNulls([S2])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each Record.FromList([S3],{"column 1"," column 2"})), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom3", "Custom", {"column 1", " column 2"}, {"column 1", " column 2"}) in #"Expanded Custom"
use this formula (I have split it into several steps to be more clear)
let
Source = Table.FromColumns({{"This is a 500 of 600", "800 of 558 available", "order has 60 of 20"}},{"Description"}),
#"Added Custom" = Table.AddColumn(Source, "S1", each Text.Split([Description]," ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "S2", each List.Transform([S1], each try Number.From(_) otherwise null)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "S3", each List.RemoveNulls([S2])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each Record.FromList([S3],{"column 1"," column 2"})),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom3", "Custom", {"column 1", " column 2"}, {"column 1", " column 2"})
in
#"Expanded Custom"