How to find and replace a value in excel

Hi All,

Have excel with below values

image

Want to replace Primary - 1 to Primary and Primary - 2 to Secondary.

Thanks,
Ula

Hi @Boopathi

say row(“UploadedName”).ToString=“Primary” and the same for secondary based on condition

Thanks
Ashwin S

Hi @Boopathi,

Kindly follow the steps below.

  1. Read Range Excel data with output var DT
  2. Take For Each Row activity &use it on DT
  3. Take If inside for each row.
    With the condition —> row(“UploadedName”).ToString="Primary - 1"
    Take assign in Then section of If activity as given below,
    row(“UploadedName”).ToString="Primary"
    And leave the Else as empty.
  4. One more If activity one another for Primary - 2.
    With the condition —> row(“UploadedName”).ToString="Primary - 2"
    Take assign in Then section of If activity as given below,
    row(“UploadedName”).ToString="Secondary"
  5. Take Write Range activity next to the for each row (outside the for each row)
    To write DT in another sheet, to ensure the output.
1 Like

On the Top of what everyone suggested - The same can be done for all values at once using UI Automation

  1. Send Hotkey (ctrl + H) : this will open the Below Menu

image

  1. Take Two Type into activities and Type values Primary 1 in Find what and Primary in Replace With

  2. Click on Replace all.

  3. Repeat the same for Primary 2 and Secondary.

OR

use BalaReva Custom Excel Activity . Below is link for Same


Mukesh

1 Like

In For Each Row:
row("UploadedName") = row("UploadedName").ToString().Replace("Primary - 1", "Primary").Replace("Primary - 2", "Secondary")

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.