Extract numbers from a string column in excel using power query

[rajpal_singh]

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 advance.

[AlienSx · Accepted solution]

Splitter.SplitTextByWhitespace()(Text.Trim(Text.Select(your_text, {" ", "0".."9"})))

[Omid_Motamedise · Accepted solution]

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"

[AlienSx · Accepted solution]

Splitter.SplitTextByWhitespace()(Text.Trim(Text.Select(your_text, {" ", "0".."9"})))

[Greg_Deckler]

@rajpal_singh Can you post that sample data as text? Seems like you could split the column based on " of " perhaps and go from there.