Need to show blank if cell contains text otherwise show the number within the cell

[Matt_Mohawk]

I am needing a formula that will show a blank value if the value in the cell is text. I also want the formula to return the value in the cell if the value in the cell is a number. Below is an example of one of the values that I am getting. There are several more but this is just an example.

[Matt_Mohawk]

The column that contains 555CBT is a Job Number field. It should only contain number values. On the website where this information is coming from, the developer has not made the field limited to only number values yet. Until that happens, I would like for the formula to remove all characters from cells that contain text. For example, if the value in the cell is 123456 then the formula will recognize this and keep it as 123456. If it says 123456R, the formula will delete everything in that cell and leave it blank.

Hopefully this helps out a little bit. If you need any additional information then just let me know.

[dhruvinushah · Accepted solution]

This does help. 
Essentially you are trying to replace the cells whose value contain non-numeric values to show blank/empty/null. 
I tested this out in Power Query, please see screenshots for steps added to "TestTable" on column "JobNumber". 
1. Source:

2. After Transformationn in Power Query:

If you have any questions let me know below. 

Please give thumbs up. Thanks!

[Matt_Mohawk]

@dhruvinushah ,

Thank you so much! I think what I saw from your image is what I need. Unfortunately I can't get it to work. See below. This is what I currently have in my Advanced Editor window. Can you tell me what I did wrong?

let
Source = #"All Tables",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Job#"})
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Job#", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Job#", null}})
in
#"Replaced Errors"

[v-xiaotang]

Hi @Matt_Mohawk 

You don't need to change the code in Advanced Editor, just do the following two steps

(1) type coercion

(2) replace

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[dhruvinushah]

In your example "555CBT" should be a text value and the column itself will be text datatype. 
Are you looking to gett 555 out of "555CBT" or does your column contain other values which are just numbers [e.g - 555551, 555552 etc.] and you want the column to be cleaned to replace values that look like '555CBT" with blank values? 
Please specify your requirement a bit better.