Excel Validation Confirmation

Help Studio
, ,
1

Hi All,

This is the first time that I am trying to do Data Validation via RPA so hoping you guy’s can help me out.

I am trying us ensure that on the excel document all Salutations in the column appear as MR. or MRS. (needs to have a dot at the end). I have done it via the Find & Replace option however when I change MR to MR. it also changes MRS to MR.S.

Is there a way that I can select the whole column and ask it to find any without a dot and then add a dot on the end?

Any suggestions will be appreciated as go live date is approaching!

Thanks :slight_smile:

2

HI @csanders

  1. Use Use Excel File → Pass the excel file path

Inside the scope

  1. Use Read Range activity → store it as DtRead

  2. Use Look up Range activity

image

  1. Use Find/Replace activity
Excel.Sheet("Sheet1").Range(LookupTotal+":"+System.Text.RegularExpressions.Regex.Match(LookupTotal,"\D+").ToString+(Dtread.Rows.Count+1).ToString)

This will look only in the particular column A1:A+DTread.Rows.Count (example)

image

Check out this XAML file

Find And Replace.xaml (11.2 KB)

Regards
Gokul

3

Try to Replace "MR " with "MR. " & "MRS " with "MRS. "

add space " " to differentiate MR from MRS. I hope it could works