Excel - Filter & Replace

Help
1

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
2

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
3

Hi @Mohansadaiyapillai

I am getting the following error…

image
image704×278 9 KB

Regards,
Shawn

1 Like
4

@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
5

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
6

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
7

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

UiPath version 2019.6.0

Thanks

1 Like
8

Hi @Palaniyappan

Thank you!

I will try this.

Regards,
Shawn

2 Likes
9

Fantastic
Cheers @shawnmurray

2 Likes
10

@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
11

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

Regards,
Shawn

1 Like
12

@Pablito,

Can you Please look in to this…

Thanks,
Mohanraj.S

1 Like
13

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
14

Thanks @Pablito That worked!!

Regards,
Shawn

1 Like
15

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

16

@shawnmurray,

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

Thanks,
Mohanraj.S

2 Likes
17

@Mohansadaiyapillai
That seems to be perfect dude!

Thanks,
Shawn

18

@shawnmurray

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

Thanks,
Mohanraj.S

1 Like
19

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

20

@shawnmurray,

Yes it is possible.

Thanks,
Mohanraj.S