Search column for anything other than numbers

[heiligbd]

I need to search and replace any letters in a column (could be aA-zZ) with a number or if the row contains only a number to leave the number.  How can I use a wildcard or something else to convert any field with any letter character and replace with zero? These are calculated columns so I can't make this change in power query.

Example rows

Column A	New Column
1500	1500
mno	0
2000	2000
abc	0
dfg	0
350	350
qrs	0
900	900

[HotChilli · Accepted solution]

Was that stated in the original post or have you edited it? If so, I apologise.

In DAX, you can use an IFERROR to assign a different value,

so can you add a new column, CONVERT to integer and assign 0 if it throws an error.

I'll let you get on with that.

[Padycosmos · Accepted solution]

Hope this helps:

[HotChilli · Accepted solution]

Was that stated in the original post or have you edited it? If so, I apologise.

In DAX, you can use an IFERROR to assign a different value,

so can you add a new column, CONVERT to integer and assign 0 if it throws an error.

I'll let you get on with that.

[HotChilli]

In Power Query, change the datatype to number.

Then replace Errors with 0.

You can do this from the interface.

[heiligbd]

I can't do it in power query. Its a calculated column outside of power query.