Forum Discussion
Extract number from a string column
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
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"
4 Replies
Please provide sample data in the format that your scenario produces. It is not clear from your example what format that is.
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"Hi nickvanmaele ,
thank you very much for your help.
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 🙂
