Replace text in excel while keeping format (date, formulas etc)

Help
1

Hi,

I am doing a regex in excel files and would like to replace these with for instance “xxx”. However, as the excel file contains many formulas and different formatting (which I want to keep) I can not output the excel datatable as a string and then replace. Does anyone have tips on how doing this the best way?

Thank you!

A

1 Like
2

Hello @anorden and welcome back to the community!

Okay, so you want to change the values in the currently opened excel file.

  1. Excel Application Score
  2. Read Range > Get the DataTable
  3. Now in the SAME application scope (we are not closing excel file) we are using For Each Row activity
  4. Using Row values, String manipulation, and regex you can now change values and store them (Maybe Replace activity)
  5. Now, use Write Cell activity to Rewrite the value in the currently opened Excel file.
    Inside Write Cell activity you need to put the new value in the place of old one.
    Your Write Cell activity should look something like

Untitled
Untitled1501×861 47.1 KB

The red square is the Row + current index
The purple square is the changed value.

I’m am sorry that I cannot do it more, but with this I think you can do it!