Replace non numeric cell values in Excel with 0

Help
, ,
1

Hello,

I would like to change the non numeric cells in column A (Number) to the value 0.

I have tried this to remove the text, but the problem is that I then get some cells that are filled with a lot of numbers instead of just the “0”. Also those cells that contain numbers as well as text need to become “0” e.g. 4/17/#4.

I have used the following method based on Excel Data Manipulation Advice

row.Item(“Number”) = System.Text.RegularExpressions.Regex.Replace(row.Item(“Number”).ToString,“[^\d+]”,“0”).ToString

What would be a good way to solve this?

Thanks in advance!

Numbers.zip (7.9 KB)

1 Like
2

Hi,

This will solve the issue
Regex.Match(yourInputString, “\d+(?:[.,]\d+)*”)
and if row(value) Contains number then it says true and based on this you can continue.
Or, put the above regex in isMatch actvity and string as input this will result in Boolean, and based on that you can continue with your process
Let us know if this helps.
Regards,
Pavan H

2 Likes
3

Hi @SJonsson

When you say :

you mean you want to replace the values that are not a valid number in the Number Column like this :

Mat ==> 0
4/17/#4 ==> 0
Or :

Mat==>0
4/17/#4 ==> 401704

??

But the values that are already numbers will still be the same?

1 Like
4

Hello @reda ,

I want to be able to do this

And that the values that are numbers should remain the same as they are.

5

Hello @pavanh003

When I tried this my numbers disappeared, but the cells with text remained.

Still working on trying to get this to work, but so far I am not succeeding.

6

Hey,
Use it in is match activity and let us know if this helps,

Regards,
Pavan H

7

Here @SJonsson

I believe this is the easiest way to do it :

Numbers.zip (17.4 KB)

Since we need to know if it is just a number it is easier to test using the IsNumber() function, no need to use a RegEx.

Also I’ve changed the paths to the local folder, change them back when you verify on the local folder you will extract from my zip.

the message box is made just to show if the value is a number or not, you can delete it after the first execution.

Hope it helps :smiley::smiley::smiley:
Regards,
Reda

3 Likes
8

Hi @pavanh003,

I have tried to use this method, but most likely I don’t use it the correct way. Thank you though!

Regards,
Sara

9

Hi @reda,

Thank you! It works great and is just what I need.

Regards,
Sara

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.