cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FabrPrado Frequent Visitor
Frequent Visitor

NEED HELP FOR EXTRACT ONLY NUMBERS

Hi, Good day for All

 

I need more one help:

 

How to extract only numbers from a column

 

|column that I have          | column that I need

Un1titled.png

 

There is no default, numeric characters can be anywhere in the text

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

Hey,

 

in the Query Editor  add a custom column and use this formula

Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

replace Column1 with the name of your column.

 

The idea, put the text of the source column to a list of characters, check each character if it is of type number, if yes keep it, else skip the character.

 

It's maybe not that obvious, but it's the pattern how regex would do it.

 

A little picture of my sample

2017-10-07_0-03-18.png

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
4 REPLIES 4
Super User
Super User

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

Hey,

 

in the Query Editor  add a custom column and use this formula

Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

replace Column1 with the name of your column.

 

The idea, put the text of the source column to a list of characters, check each character if it is of type number, if yes keep it, else skip the character.

 

It's maybe not that obvious, but it's the pattern how regex would do it.

 

A little picture of my sample

2017-10-07_0-03-18.png

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
FabrPrado Frequent Visitor
Frequent Visitor

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

 

Very good, working;

Thank TomMartens

 

See how my table is:

Capture.PNG

Highlighted
Giavo Regular Visitor
Regular Visitor

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

Tom, this is a great solution, I have tried it and it worked but in my case I have a small problem: the numbers contained in my text column are DECIMAL number and your solution extracts the numbers and deletes the "." and gives an integer as a result (so 1.0.2.1 is returned as 1021). How would it be possible to keep the original format of numbers ?
JPBlack New Member
New Member

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

Giavo, try this

 

Text.Select([SourceColumn], {"0".."9","."})

 

And a simple way to extract only numbers from a column, would be Text.Select([SourceColumn], {"0".."9"})