How to Clear the Unwated Characters from a column?

[Israel_Silva]

Hello everybody,

I need to remove the “SVE-0” character from the “Name” column.

I even got a solution through a for each and substring, but as the column has many rows, the process was long, is there any solution that applies the correction to the entire column faster?

Dyna.xlsx (154.4 KB)

[Gokul001 (Gokul Balaji)]

Hi @Israel_Silva

You can try with this LINQ expression

Dt.AsEnumerable().Select(Function(a) Dt.Clone.LoadDataRow({a.Field(OF String)("Name").ToString.Replace("SVE-0","")},False)).CopyToDataTable

Regards
Gokul

[Israel_Silva]

Your solution worked, but the following message occurred:

Write Range: Cannot write to the target range because it has hidden rows. Remove any filters or unhide rows and try again.

[Gokul001 (Gokul Balaji)]

@Israel_Silva

Write Range: Cannot write to the target range because it has hidden rows. Remove any filters or unhide rows and try again.

Have you tried with Excel Application scope and followed by Write Range

Check out this thread

How to solve this Write Range error : Cannot write to the target range because it has hidden rows. Remove any filters or unhide rows and try again?

Hi, ExcelWriteRange throws exception if there is hidden column or row, as the specification of Excel activities package 2.8.5 or above. So, downgrade Excel activities package to 2.8.4 or lower might resolve this issue. Or can you try to write range to another sheet, then copy it to the target area using other activity? Regards,

Regards
Gokul

[Israel_Silva]

Unfortunately, I haven’t been successful yet. Even applying the solutions described in the link

[Gokul001 (Gokul Balaji)]

Have you tried to write the output data in the different Sheet or Workbook? @Israel_Silva

[Israel_Silva]

Output data below:

Code below:
ReadExcel.xaml (9.7 KB)

[Gokul001 (Gokul Balaji)]

Hi @Israel_Silva

Check out this XAML file

Find And Replace.xaml (11.3 KB)

Regards
Gokul

[supermanPunch (Arpan)]

Hi @Israel_Silva ,

Similar to @Gokul001 's Suggestion, but instead of using Read Range, we might be able to get the Total Count of the Row using the Find First /Last Data Row and then use the Find /Replace Value Activity.

The above is assuming the Excel Column name of Nome Column is already known (i.e E), if not known we should also be able to get the column name.

Let us know if you are able to get the required output using the provided suggestions.