How to replace special characters in excel

Help Something Else
1

I have mentioned below characters to replace in Excel

currentRow.ToString.Replace(“#”,“”).Replace(“”,“A”).Replace(“+”,“”).Replace(“*”,“”).Replace(“$”,“”).Replace(“;”,“”).Replace(“/”,“”)

2

Hi @sathish_Kumar6

Try this:

System.Text.RegularExpressions.Regex.Replace(Input,"[#+*$;/Â]","").Trim()

Hope it helps!!

1 Like
3

@sathish_Kumar6

currentRow.ToString().Replace("#", "").Replace("Â", "A").Replace("+", "").Replace("*", "").Replace("$", "").Replace(";", "").Replace("/", "")
1 Like
4

But i want for  is A

5

Hi @sathish_Kumar6

currentRow.ToString.Replace("#","").Replace("Â","A").Replace("+","").Replace("*","").Replace("$","").Replace(";","").Replace("/","")
1 Like
6

Hi @sathish_Kumar6

Try this:

System.Text.RegularExpressions.Regex.Replace(Input,"[#+*$;/]","").ToString.Replace("Â","A").Trim()

Hope it helps!!

1 Like
7

im getting this in message box

image

8

@sathish_Kumar6

currentRow.ItemArray.Select(Function(item) If(TypeOf item Is String, DirectCast(item.ToString().Replace("#", "").Replace("Â", "A").Replace("+", "").Replace("*", "").Replace("$", "").Replace(";", "").Replace("/", ""), String), item)).ToArray()

I hope this helps you

You want to replace all rows and columns Special characters so above will help you

1 Like
9

Hi @sathish_Kumar6

Or try this in Invoke Code:

dataTable.AsEnumerable().ToList().ForEach(Sub(row) 
                                            row("ColumnName") = row("ColumnName").ToString.Replace("#", "").Replace("Â", "A").Replace("+", "").Replace("*", "").Replace("$", "").Replace(";", "").Replace("/", "")
                                          End Sub)

In Invoke Arguments:

dataTable || In/Out || System.Data.DataTable || yourdatatableName

Regards

1 Like
10

yes, I want to replace all rows only, and what to assign on the left? because I’m getting error as

image
image821×520 27.7 KB

11

@sathish_Kumar6

If you want to replace one column then use this in for loop

Assign:
   To: modifiedRow
   Value: row("ColumnName").ToString().Replace("#", "").Replace("Â", "A").Replace("+", "").Replace("*", "").Replace("$", "").Replace(";", "").Replace("/", "")

2nd Approach

Perform read range
If you want to replace special characters to null to all rows and columns in datatable simply use this linq query

Dt=(From row In Dt.AsEnumerable()
                    Select Dt.Clone().Rows.Add(row.ItemArray.Select(Function(col) col.ToString().Replace("#", "").Replace("Â", "A").Replace("+", "").Replace("*", "").Replace("$", "").Replace(";", "").Replace("/", "")).ToArray())).CopyToDataTable()
1 Like
12

okay but 1) I have like 167 columns so I want to replace it without mentioning column names

  1. left side assigning modifiedrow is enough?

Assign:
To: modifiedRow
Value: row(“ColumnName”).ToString().Replace(“#”, “”).Replace(“”, “A”).Replace(“+”, “”).Replace(“*”, “”).Replace(“$”, “”).Replace(“;”, “”).Replace(“/”, “”)

13

@sathish_Kumar6

You have 167 columns then try my second approach it will check all 167 columns if it find special characters then it will replace special characters to nul.

Read range
Assign activity---->Linq auery
Write range

14

I tried this LINQ query but the values not changing so for example I have mentioned a few samples

Ticked id column work column

#TDHF0131435 +919896546554

15

@sathish_Kumar6

The above linq query is working fine for me
Sequence21.zip (1.7 KB)
Input:
image
Output
image

1 Like
16

Try this:-

row(“YourColumnName”) = System.Text.RegularExpressions.Regex.Replace(row(“YourColumnName”).ToString(), “[^a-zA-Z0-9\s]+”, “”)

1 Like
17

Try this:-

dtExcelData = (From row In dtExcelData.AsEnumerable()
Let newRow = row.ItemArray.Select(Function(cell) System.Text.RegularExpressions.Regex.Replace(cell.ToString(), “[^a-zA-Z0-9\s]+”, “”))
Select dtExcelData.Rows.Add(newRow.ToArray())).CopyToDataTable()

1 Like
18

Thanks it working… apologies i did’nt check well

19

how to use this for 167 columns

20

(From row In dtExcelData.AsEnumerable()
Let newRow = row.ItemArray.Select(Function(cell) System.Text.RegularExpressions.Regex.Replace(cell.ToString(), “[^a-zA-Z0-9\s]+”, “”))
Select dtExcelData.Rows.Add(newRow.ToArray())).CopyToDataTable()

The LINQ query which I provided will work for all columns in one go. It iterates through each row in the DataTable (dtExcelData ), processes each cell in every row to remove special characters using a regular expression, and then creates a new DataTable (newRow.ToArray() ). This process is applied to all rows and columns in the original DataTable. Therefore, it will handle all 167 columns in one go.

1 Like