Filter and Extract values from excel rows

[gokul1904 (Glory 01)]

FCR.xlsx (12.8 KB)
In attached excel for a Supplier name “RGD” I want to extract the “Spend Category(SC)” and “Cost Center(CC)” value as SC01438 and CC2103 from the rows and there is another default value of “Region” as “USA”

Basically spend category must be the value inside the brackets “()” and cost center ‘the first word of cost center category’ i.e CC2103.

Incase supplier name is not found in the list the values of SC and CC must be as below :
SC - Multi Region
CC - Supplies
Region - USA

Can you please help in finding a solution for this ?

[Gangadhar_Athili]

Var1(Int32)=DT.Asenumerable.Count(Function(r) r(“Supplier Name”).Tostring.equals(“RGD”))

If
Var1>0
Then

DT1=DT.Asenumerable.Where(Function(r) r(“Supplier Name”).Tostring.equals(“RGD”)).Copytodatatable

Str1= Regex.Match(DT1(0)(“Spend Category”).Tostring,"((.*?))).Group(1).Value

Str2= Regex.Match(DT1(0)(“Cost Centre”).Tostring,“^[1]+”).Value

Str3=“USA”

---

1. 0-9A-Z ↩︎

[gokul1904 (Glory 01)]

Hey ,

Thank you for the response.
Str1 is not giving the proper output . It is giving only “S”.

Regards,
Gokul

[Gangadhar_Athili]

Updated the Query Now Try it Once …And let me Know if you face any challenges

[gokul1904 (Glory 01)]

@Gangadhar_Athili

Regex.Match(DT1(0)(“Spend Category”).Tostring,"((.*?))).Group(1).Value

I am getting the below error -

Regards,
Gokul

[Gangadhar_Athili]

[Gokul001 (Gokul Balaji)]

Hi @gokul1904

How about this expression?

To get the Spend Category

System.Text.RegularExpressions.Regex.Match(YourString,"(?<=\().*(?=\))").Tostring

To get the Cost Center

System.Text.RegularExpressions.Regex.Match(YourString,"^(\D+\d+)").Tostring

Regards
Gokul