Forum Discussion
PowerQuery | Extract Numbers from A string (eg ABCD1234)
HI ovetteabejuela,
You can refer to below formula to if it suitable for your requirement.
Logic: split text to character list, select the number part and merge them to text.
Functions comment.
Text.ToList: split text to character list.
Values.Is: check value type.
List.Transform: transform list from original list.
List.RemoveNulls: remove replaced null value.
Text.Combine: merge character list to text.Formula:
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null))))
Full query:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxxxx\Desktop\test.xlsx"), null, true), Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet3_Sheet,{{"Column1", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))) in #"Added Custom"Regards,
Xiaoxin Sheng
Hi Anonymous, great solution! :)
Taking this post as an opportunity, could you please try to help with two extra details maybe complementing your solution?
1- Is there any way to separate number from text (vise-versa), but keep both in different columns?
2- A different approach as follow attached. Is it possible or this is ask too much? :)
Above is the problem and desired outcome as bellow
Thanks a lot in advance and cheers,
Jimmy
I'm interested to see somebody solving this. This is a tough one.
Using Transpose and then Split column Space Right most delimiter in M Is manually intensive but will do what you want. I also trimmed the extra spaces
Heres my code
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column3.1", "Column3.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column3.1", type text}, {"Column3.2", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Column4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column4.1", "Column4.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column4.1", type text}, {"Column4.2", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type3",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Trimmed Text",{{"Column2", type text}, {"Column5", type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Changed Type4",{{"Column2", Text.Trim, type text}, {"Column3.1", Text.Trim, type text}, {"Column3.2", Text.Trim, type text}, {"Column4.1", Text.Trim, type text}, {"Column4.2", Text.Trim, type text}, {"Column5", Text.Trim, type text}}),
#"Transposed Table1" = Table.Transpose(#"Trimmed Text1")
in
#"Transposed Table1"Thnaks a lot bwasham:smileyhappy:
Hello,
a simple one step answer to this question:
OneStep = Table.AddColumn(Source, "MyNumberColumn", each Text.Select([MyStringColumn], {"0".."9"}))
