Replace Activity to remove special character

Hi All,

I am comparing two excel sheets and trying to find match. I have used replace activity to convert the date in both the sheets in one format and replace special characters. However, the replace activity is not working as expected. Please find the below screenshot.

If((ColumnDisc(column.ColumnName)=“Start_date"Or ColumnDisc(column.ColumnName)=“End_date”),DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim,{“M/d/yyyy”,“M-d-yyyy”,“MM/dd/yyyy”,“MM-dd-yyyy”},System.Globalization.CultureInfo.InvariantCulture).ToString(“ddMMyyyy”),CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.replace(”$.“,”“).Trim) = If((column.ColumnName=“Start Date” Or column.ColumnName=“End Date”),DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString(“ddMMyyyy”),dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Replace(”$.“,”").Trim)

I am trying to convert the date in both sheets to ddMMyyyy and replace the special character i.e. “.” and replace dollar sign $.

Hi @marina.dutta

I could see there are some typo error. May be you could try this code:

If ((ColumnDisc(column.ColumnName) = "Start_date" Or ColumnDisc(column.ColumnName) = "End_date"), DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim, {"M/d/yyyy", "M-d-yyyy", "MM/dd/yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString("ddMMyyyy"), CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Replace("$.", "").Trim) = If ((column.ColumnName = "Start Date" Or column.ColumnName = "End Date"), DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString("ddMMyyyy"), dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Replace("$.", "").Trim)

Hope it helps

@Parvathy

The replace function did not worked

@marina.dutta

Try this once:

If ((ColumnDisc(column.ColumnName) = "Start_date" Or ColumnDisc(column.ColumnName) = "End_date"), DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim, {"M/d/yyyy", "M-d-yyyy", "MM/dd/yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString("ddMMyyyy"), CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Replace("$", "").Replace(".", "").Trim) = If ((column.ColumnName = "Start Date" Or column.ColumnName = "End Date"), DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString("ddMMyyyy"), dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Replace("$", "").Replace(".", "").Trim)

Regards

@Parvathy

I tried but the replace the function did not work

Hi @marina.dutta

Try this syntax

If ((ColumnDisc(column.ColumnName) = "Start_date" Or ColumnDisc(column.ColumnName) = "End_date"), DateTime.ParseExact(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString.Trim, {"M/d/yyyy", "M-d-yyyy", "MM/dd/yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString("ddMMyyyy"), System.Text.RegularExpressions.Regex.Replace(CurrentRow.ByField(ColumnDisc(column.ColumnName)).ToString, "[$.]", "").Trim) = If ((column.ColumnName = "Start Date" Or column.ColumnName = "End Date"), DateTime.Parse(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString.Trim).ToString("ddMMyyyy"), System.Text.RegularExpressions.Regex.Replace(dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString, "[$.]", "").Trim)

Regards

@Parvathy

Thanks a lot

1 Like

You’re welcome @marina.dutta

Happy Automation
Regards

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