Excel - Filter & Replace

Hi,

I have a spreadsheet in Excel which when I use the filter function and i want to filter to show only the “blank” cells and then replace the “blanks” with a word.

Is this something that can be done?

Regards,
Shawn

2 Likes

Hi @shawnmurray,

I have created a workflow based on your requirement.

Please let me Know if it is exact one.

Thanks,
Mohanraj.SEmpty.zip (173.3 KB)

3 Likes

Hi @Mohansadaiyapillai

I am getting the following error…

Regards,
Shawn

1 Like

@shawnmurray,

Can you Please confirm Your UiPath Version and also ensure the below dependencies is added to this project.

Capture
Thanks,
Mohanraj.S

2 Likes

HI @shawnmurray

Few steps to do this…

  1. Use read range to read the excel to a datatable
  2. Use Filter Data Table activity to filter the datatable with blank. You can configure from which value you need to filter and from which column
  3. The result, you can use in a For Each Row activity
  4. Within the actiivty, use an Assign activity to replace the blanks with a new word

Code for assign is:
row(“YourColumn”) = “New Text”

2 Likes

Hi
Hope these steps could help you resolve this
–use excel application scope and pass the file path as input
–use read range activity and get the output with a variable of type datatable named outdt
–use a for each row loop and pass the above variable as input outdt
–inside the loop use a activiy called **if **
–with condition like this
String.IsNullOrEmpty(row(“yourcolumnname”).ToString)

and if the above condition passes it will go to the THEN part where we can have a assign activity to pass the blank with the word we want
like this
row(“yourcolummname”) = "yourstring"

simple isn’t it…
Cheers @shawnmurray

2 Likes

@Mohansadaiyapillai how can i check the dependencies if the product does not open.

UiPath version 2019.6.0

Thanks

1 Like

Hi @Palaniyappan

Thank you!

I will try this.

Regards,
Shawn

2 Likes

Fantastic
Cheers @shawnmurray

2 Likes

@shawnmurray Try to upgrade it to the latest version 2019.7.0 and open the project.

( Sorry you cannot see the dependencies if it is not open)

Thanks,
Mohanraj.S

2 Likes

@Mohansadaiyapillai To upgrade would i uninstall current version and download new version from Community?

Regards,
Shawn

1 Like

@Pablito,

Can you Please look in to this…

Thanks,
Mohanraj.S

1 Like

Hi @shawnmurray,
If you are using Community Edition it should automatically download the latest version. If this is not happening, go to https://platform.uipath.com and after you will log in go to:
image

1 Like

Thanks @Pablito That worked!!

Regards,
Shawn

1 Like

Hi @Mohansadaiyapillai ,
After updating i have been able to look at this.
This is what i am looking for but instead of a certain range, is it possible to apply it to an entire column, so column “A” for example.

Regards,
Shawn

@shawnmurray,

Please look into this and it will give you a solution.
EmptyCell.zip (174.3 KB)

Thanks,
Mohanraj.S

2 Likes

@Mohansadaiyapillai
That seems to be perfect dude!

Thanks,
Shawn

@shawnmurray

That’s Sounds good, Please close this thread as mark my answer as solution.

Thanks,
Mohanraj.S

1 Like

Hi @Mohansadaiyapillai
Could i apply the same principle for example if a number was greater than 10 to replace it with a word?

Regards,
Shawn

@shawnmurray,

Yes it is possible.

Thanks,
Mohanraj.S