How can we replace a entire column in excel?

Hi @ All,

I have excel file in that one column I need to replace with Special characters.
How the way we can do?
without using for each row

1 Like

Hi @varunk,

Refer this link,

https://www.extendoffice.com/documents/excel/3483-excel-remove-special-characters.html

Use Excel Application scope and use send hot key to replace special characters.

Regards,
Arivu

@varunk
As of my knowledge you have to use for each to update the values.
You can use Queries only to retrieve the data.

Regards,
Mahesh

Hi @varunk,
You read the excel as datatable and do the for next loop . You can use the regex format by replace the special characters inside the for next loop . like this Regex.Replace(str, “[^a-zA-Z0-9_.]+”, “”, RegexOptions.Compiled)

Finally use the write range to update the excel sheet.

Regards
Balamurugan.S

Hi @balupad14

I have 3 columns are there in excel,I want to replace second column with third column.

@varunk

I think you can try like this
dt1=dt.DefaultView.ToTable(False,“Column 1”,“Column 3”)

dt1.Columns(1).ColumnName=dt.Columns(1).ColumnName

Regards,
Mahesh

1 Like

Hi @MAHESH1 @varunk ,
I have tested the code . really do we need “dt1.Columns(1).ColumnName=dt.Columns(1).ColumnName” this one.

Below I have attached the xml for test.

File : How can we replace a entire column in excel.zip (9.3 KB)

Regards
Balamurugan.S

@balupad14
Why I used “dt1.Columns(1).ColumnName=dt.Columns(1).ColumnName” means
because If am not going to use this it will have the 3rd column name .
So iam assigning the column name with Second Column Name.

Regards,
Mahesh

1 Like

@MAHESH1,
yes you are right.

Here the exact sample : How can we replace a entire column in excel.zip (8.7 KB)

Regards
Balamurugan.S