How to Convert -ve values into 0 in some column of Excel?

Help Activities
, ,
1

How Can I convert -ve values to 0 in excel column ? Please help me here.
Working on Excel with bigger data and multiple sheets.
Thanks in advanced.

2
  • Excel Process Scope
    ** Use Excel File
    *** For Each Excel Sheet
    **** For Each Excel Row
    ***** If CurrentRow("columname).ToString = “-ve” Then Assign CurrentRow(“columname”) = “0”

image
image472×779 24.6 KB

Some of the options and details, like the Has Headers, will depend on your exact situation.

3

HI,

Another approach: how about using Find/Replace Value activity as the following?

image
image589×614 15.6 KB

Regards,

4

Which Excel version are you using ?

5

Which Excel version are you using ?

6

Excel version doesn’t matter. Why are you asking? Are you not seeing these activities? If not, then upgrade your UiPath.Excel.Activities dependency.

7

I am not seeing these activities that’s why I am asking. I also upgraded excel activities dependecy but still not getting it.

8

To what version of the Excel package? What version of Studio are you on? Is your project set to modern?

9

Studio Version : 2023.10.0
Excel version : 2.22.2

10

Hi @PALKUMARI_PATEL ,

Could you check if the Modern Design is enabled or If not Selected in Filter ?

1 Like
11

Hi, @supermanPunch Thanks for the suggestion, by enabling Modern Design it is showing the activities.

12

This is not giving me an error but not giving me the output that I want. My negative values are still there.

13

Show us your code so we can see how you have everything configured.

14

image

File is .xlsm

15

I’m not sure if providing a range like H:H will work. Try just putting A1 for the Range and see what happens. Might also try making the 0 a string so “0” instead of just 0. Also, is the value you’re looking for exactly -ve with no extra spaces or other characters?

16

How Can I provide particular sheet where I want to change the values ? I have 3 sheets and want to use 1 sheet only to modify.

17

Yes, the values are exactly -2,-3 like this without any extra space.

18

Then why are you using -ve as the value to find?

19

So What Can I Do to get the -ve values and convert into 0?

20

You have to tell the Find/Replace value activity exactly what value to replace.

If your logic is just any negative value, then you have to use my original suggestion and loop through the rows checking the value of the column (and converting it to INT32).