Forum Discussion
PowerQuery | Extract Numbers from A string (eg ABCD1234)
Hi Community,
In this example, FirstName12345. How can I extract 12345 from it using M?
Assume that the number length varies (eg abc123, abc1234567, abc1234, etc...)
[Edit] Spelling
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
14 Replies
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
Anonymous thank you very much for the solution, I really could have not thought of that. I'll read more about it "Transform".
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.
I have a similar problem. My text looks like "Submission late by 10 days"; "Overdue by -9 days". So my numbers can be positive or negative. Any help?
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.From(Text.Select([Remarks],{"0".."9","-"}))) in #"Added Custom"Hope this helps.
