Match two excel columns and write to new column( advanced)

[Duleepa_Krishan]

Hi all, Again I am stuck with some UIpath activity, I read excel data table and I am referring here two columns named “client reference” & “comment”. And my automation requirements as follows;

1. The column named “comment” contains some values like “1988411_YM2021-189” and that particular value need to be separate to read as “1988411”(separate from special character “_” and take leading values only) and write on same column (“comment”) .
2. Also same column (“comment”) if contains some values that starts from alphabetic characters like “GB2021-378” , “WV21587”, “MG2021-2712” , then those values need to be replaced with values which are in “client reference” same row and write it to “comment” column as well.

I here attached the screen shots which describes the questions I mentioned above. Also attached herewith the excel data table

Hope you understand my question and your kind attention & solution in this regard much appreciated.

Thanks in advance.

Requirement 01.

Requirement 02.

Data Table.xlsx (18.2 KB)

[prasath17 (Guru)]

@Duleepa_Krishan - Here you go…

My Sample Output

Here’s how you have to code it…

If Condition

CurrentRow("comment").ToString.Contains("_")

Inside Then Condition

CurrentRow("comment")  = System.Text.RegularExpressions.Regex.Match(CurrentRow("comment").ToString,"\d+(?=_)").Value

Inside the Else write an another if condition(This will check if first 2 characters are alphabets)

system.Text.RegularExpressions.regex.Match(CurrentRow("comment").ToString,"^[A-z]{2}").Success

Inside this Else’s Then condition(This is will assign the Client Refrence value to Comment Column)

 CurrentRow("comment") = CurrentRow("Client Reference").ToString

Inside this Else’s Else condition

 CurrentRow("comment") = CurrentRow("comment")

Hope this helps…

[Duleepa_Krishan]

It worked!!!.. Thank you so much Mr. Prasath as always you save my day and I am getting learning new things as well. Once again thank you and really appreciate your time.