How to smartly remove some special characters and some strings (if those exist) from column data in a datatable?

I have a DataTable filled with lots of data. Under a column named “Description”, we have all kinds of data, but sometimes it contains a random symbol accidentally typed. I want to get rid of those accidentally-hit symbols from the cells under Description column.
It is not all symbols that need to be removed. The symbols that need to be removed if exists are :, ;, and ,.

If the description data contains one or more of those symbols at the beginning or the end of the cell data, they need to be removed.

.example description needs to be modified to example description
example,; description doesn’t need to be modified.
example description; needs to be modified to example description
;example description::; needs to be modified to example description
and so on.

How could I do this smoothly?

For Each row in dt_myData:
If row.Item(“Description”).ToString.Trim.StartsWith(":") Or row.Item(“Description”).ToString.Trim.EndsWith(":") Then
… … Assign: row.Item(“Description”) = row.Item(“Description”).ToString.Trim.Replace(":", “”)

The above code is kinda of too long, especially if you need to check them for each symbol. Is there a better way?

Also, I want to get rid of some strings (if exist) from this column as well. If the description starts with certain strings, they need to be removed. Like

Description: Example Description needs to be modified to Example Description

Some data under “Description” column contains Description:, or Description - or Desc: and so on (There are more). These are redundant, so they need to be gotten rid of. How could I do this? Should I just use replace for each case? Do I need to Assign for each case one by one?

Hi @tomato25,

you can use the regular expression,

2 Likes

@tomato25, you can use this regex pattern - [^0-9a-zA-Z\s]+

Cheers!!

1 Like

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