How to replace particular word from string in excel without using For each

Input:

Expected Output:

1 Like

Hi @NervousTuber

Try the below expression for Replace using Regex expression

System.Text.RegularExpressions.Regex.Replace(“Reviewed in India on 20 July 2020”,".*(?=\d{2}\s\S+\s\d{4})","").ToString

Regards
Gokul

i dont want to use for each …
i was following this method but its not working for me

@NervousTuber
Try the below expression

dt = dt.AsEnumerable().Where(Function(a) a.Field(of string)(“yourcolumnname”).ToString.Replace(“Reviewed in India on 20 July 2020”,” *(?=\d{2}\s\S+\s\d{4})”).ToString).CopyToDatatable()

Regards
Gokul

image

Can you share the xaml and excel file @NervousTuber

Regards
Gokul

Amazon_Reviews_290122.xlsx (26.5 KB)
Test.xaml (8.7 KB)

Hi @NervousTuber

Try the below expression

Dt.AsEnumerable().Select(Function(row) row(“Date”).ToString.Replace(System.Text.RegularExpressions.Regex.Match(“Reviewed in India on 20 July 2020”,".*(?=\d{2}\s\S+\s\d{4})").ToString, “”)).ToArray

Regards
Gokul

can you share xaml?

Hi @NervousTuber

You should declare the variable as String[Array]

you need to increment the process Dt(0)

Regards
Gokul

How to write final data into sheet?

Hi

Use this simple expression to do the replace text in a column and get the output as datatable

dt = (From r In dt.AsEnumerable
Select ia = r.ItemArray.ToList
Select ic = ia.ConvertAll(Function (e) system.Text.RegularExpressions.Regex.Replace(e.ToString.Trim, “(Received).*(\d{2}\s\S+\s\d{4})”, “ ”)).ToArray
Select dt.Rows.Add(ic)).CopyToDataTable()

Cheers @NervousTuber

image

Getting this error

Make sure you have typed from your end as the direction of double quotes will change if being copied from clipboard

@NervousTuber


Yeah i already did this still getting same error

1 Like

Hey @NervousTuber

Kindly try this please…

Thanks
#nK