Extract number from a string column

[Learner22]

Dear community,

I have a column with text and numbers in a table. I would like to have the numbers in a separate column. What is the best was to do this?

Here are two examples

1. 

xxxxxxxxx

34

xxxxxxxxx

ordernumber: 8100006833

xxxxxxxxx

45

xxxxxxxxx

2.

xxxxxxx

28

xxxx 42 xxx

xxxxxxx

ordernumber: 8100006922

xxxxxxx

19

xx

xxxxxx

xxx

x: text

I need the ordnernumber in a column.

Thank you very much for your support.

Best regards

[Einomi]

I have replicated your data, I converted it into a table, the header name is Sample

Please give it a try 

let
  Requête = Excel.CurrentWorkbook(),
  #"Content développé" = Table.ExpandTableColumn(Requête, "Content", {"Sample"}, {"Sample"}),
  #"Colonnes supprimées" = Table.RemoveColumns(#"Content développé", {"Name"}),
  #"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Personnalisé", each try Text.Select ([Sample], {"0".."9"} ) otherwise Text.From([Sample]))
in
  #"Colonne personnalisée ajoutée"

Let us know

PS : please mark this post as a solution if it helps and we welcome kudos 🙂

[nickvanmaele · Accepted solution]

Hi @Learner22 

Purely based on your example, I would first filter the column to the rows that contain the word "ordernumber". 

On that filtered table, you can split the column based on the colon (":"), 

and finally trim the number to get rid of leading and trailing spaces. 

If I create an Excel table called Table1, like this:

Input

xxxxxxxxx

34

xxxxxxxxx

ordernumber: 8100006833

xxxxxxxxx

45

xxxxxxxxx

Then, from the Excel table, if I create a Power Query by going to Data - From Table/Range, here is a recipe to extract the order number:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Input], "ordernumber")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Input", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Input.1", "Input.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Input.2", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Input.2", Int64.Type}})
in
    #"Changed Type1"

[Learner22]

Hi @nickvanmaele ,

thank you very much for your help.

[Syndicate_Admin]

Please provide sample data in the format that your scenario produces.  It is not clear from your example what format that is.