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

[Vinit_Mhatre (BotBuilder)]

Input:

Expected Output:

[Gokul001 (Gokul Balaji)]

Hi @Vinit_Mhatre

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

[Vinit_Mhatre (BotBuilder)]

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

How to replace a value in datatable in Uipath without using for each row

Hi Welcome back to uipath community There are actually two ways of doing that —first is convert the datatable to a string with the help of OUTPUT DATATABLE ACTIVITY and pass the datatable as input and get the output variable of type string named str_output —now use a normal assign activity like this str_output = str_output.ToString.Replace(“your old value”,” your new value”) Now use GENERATE DATATABLE ACTIVITY and mention the str_output variable as input And get the output with a variabl…

[Gokul001 (Gokul Balaji)]

@Vinit_Mhatre
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

[Vinit_Mhatre (BotBuilder)]

@Gokul001

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()

[Gokul001 (Gokul Balaji)]

Can you share the xaml and excel file @Vinit_Mhatre

Regards
Gokul

[Vinit_Mhatre (BotBuilder)]

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

[Gokul001 (Gokul Balaji)]

Hi @Vinit_Mhatre

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

[Vinit_Mhatre (BotBuilder)]

can you share xaml?

[Vinit_Mhatre (BotBuilder)]

@Gokul001

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

[Gokul001 (Gokul Balaji)]

Hi @Vinit_Mhatre

You should declare the variable as String[Array]

you need to increment the process Dt(0)

Regards
Gokul

[Vinit_Mhatre (BotBuilder)]

@Gokul001

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

How to write final data into sheet?

[Palaniyappan (Palaniyappan P)]

@Gokul001

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

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 @Vinit_Mhatre

[Vinit_Mhatre (BotBuilder)]

Getting this error

[Palaniyappan (Palaniyappan P)]

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

@Vinit_Mhatre

[Vinit_Mhatre (BotBuilder)]

@Palaniyappan

(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()

Yeah i already did this still getting same error

[Nithinkrishna]

Hey @Vinit_Mhatre

Kindly try this please…

Find/Replace Value

Thanks
#nK

[parvathy.g.s (Parvathy G S Ext)]

Hi Palaniyappan,

Could you please help me with the automation for values in the below highlighted column(Col D) based on other columns…

Any help appreciated. Thanks in advance!

#uipath #rpa-discussions #excel-tables