Removal of unwanted words from excel and replace with blank with specific color

Learning & Development Programs Automation Starter
, , ,
1

Hi all,
image
This is my sample input excel.
In excel if we are getting any other words other than highlighted words (refer, Control, list, Vision) that particular cell should be written as blank.
Expected output.
image
How to do this.
Please help.

Regards,
Lakshmi

1 Like
2

Hi Bro.

You can use the VBA code to resolve your sample.

3

Hi @Mr.H ,
Can you explain how to do that.
I tried out by using for each loop but

image
image629Ă—598 14.5 KB

I am not getting what to write in RHS of assign.
Please help.
Thanks,
Lakshmi

4

@Mr.H ,
Can you explain me, how to do in VBA.
Regards,
Lakshmi

1 Like
5

Hey!

Try this:

  1. Take 3 assigns
Assign ColB = CurrentRow("Col B").ToString.Trim.ToLower 

Assign ColC = CurrentRow("Col C").ToString.Trim.ToLower 

Assign ColD = CurrentRow("Col D").ToString.Trim.ToLower

If Condition do like this:

ColB.Equals("refer") and ColB.Equals("vision") and ColB.Equals("control") or ColC.Equals("refer") and ColC.Equals("vision") and ColC.Equals("control") or ColD.Equals("refer") and ColD.Equals("vision") and ColD.Equals("control")

In then Block

Assign ColB = ColB.Replace(ColB,"").ToString

Assign ColC = ColC.Replace(ColC,"").ToString

Assign ColD = ColC.Replace(ColD,"").ToString

Regards,
NaNi

6

Hi Bro.

Pls check sample as attached file.
Book1.zip (14.7 KB)

7

Hi @THIRU_NANI
Can you please check this workflow.
Sequence replace wrks2.xaml (10.9 KB)
In output file its copying the same input file.
image
Thanks,
Lakshmi

8

Hey!

Could you paste the input excel here?

Regards,
NaNi

9

Hi,
Book1.xlsx (9.1 KB)
Please look on it.
Regards,
Lakshmi

10

Hi @THIRU_NANI ,

What changes i need to do in my workflow.

Regards,
Lakshmi

11

Hey!

The solution which I have provided it’s working…

Just use the write cell activity inside the if condition…

Before that you’ve to create the counter variable…

And in the end increament the counter

Regards,
NaNi

12

Hi,
i tried but output am getting same as input file.
Counter variable and write cell given.

Regards,
Lakshmi

13

Hey!

Can you check it in debug mode?

And the Operation make it to OR instead of AND

Regards,
NaNi

14

hi @THIRU_NANI ,
Values are changing in excel but getting this error.

image
image1361Ă—654 47.1 KB

image
What changes i need to do.

Regards,
Lakshmi

15

Hello @lakshmi.mp

Just enable continue on error for that activity to True. Or you can put it inside a Try catch as well.

I think for some rows the value is null and as per the expression it is trying to replace, thats why you are getting that error.

16

Hey!

We have the value in ColB right… instead of using the same variable can we try to assign the replace thing to another variable…

Find the attached image below…

Replaced
Replaced513Ă—534 16.7 KB

And also use 3 writecell activities to write the replaced value to the excel…

Find the attachment below…

Counter
Counter591Ă—615 18 KB

Initialize counter with 1

Regards,
NaNi

17

@THIRU_NANI ,
ColB.Equals(“refer”) or ColB.Equals(“vision”) or ColB.Equals(“control”) and ColC.Equals(“refer”) or ColC.Equals(“vision”) or ColC.Equals(“control”) and ColD.Equals(“refer”) or ColD.Equals(“vision”) or ColD.Equals(“control”)
above if condition,
image
showing output like this.
ColB.Equals(“refer”) and ColB.Equals(“vision”) and ColB.Equals(“control”) or ColC.Equals(“refer”) and ColC.Equals(“vision”) and ColC.Equals(“control”) or ColD.Equals(“refer”) and ColD.Equals(“vision”) and ColD.Equals(“control”)
for this condition,
The output is same as input
image
Above changes have done.

Regards,
lakshmi

18

Hi,

Even after using try catch block. Getting error.
Sequence replace wrks2.xaml (25.6 KB)

Regards,
Lakshmi