If cell is number then cell, else empty

[Alex_]

Hi! I have some data like the following:

%

25

15

Some text

Words typed

75

I need to work with just the numbers in the column. How can I delete the text data without removing the whole row? A new column like the following would be ideal, but it returns "" in every row (I suspect it is testing if the whole column is numbers).

Just% = IF(ISNUMBER([%]), [%], "")

if Value.Is([%], Int64.Type) then [%] else ""

[jdbuchanan71 · Accepted solution]

@Alex_ 

You are better off doing the transformation in powerquery I think.  It is 3 steps to get to what you are looking for.

• Duplicate your column
• Convert the new column to decimal data type (this will make all text values error)
• Replace errors with null.

I have attached my sample book for you to look at.  Right click on the table and go to Edit Query to see the steps.

[Alex_]

Thanks - this worked perfectly!

[amitchandak]

@Alex_ , refer if this code in the blog can help

https://eriksvensen.wordpress.com/2018/03/06/extraction-of-number-or-text-from-a-column-with-both-te...

[Alex_]

Hi @amitchandak

Thanks for your help! Unfortunately this doesn't work as there are some dates mentioned within the text. I need to show the value if there is only numbers, and nothing if there is a mix.