Get information out of a String using Keywords and Comma, having amounts

[JayLee (Jay)]

Hello community,

i am new on this platform and hope you can help me with my problem. Reading different solvings of string manipulations and other topics, i can´t find a solution for my special problem.

What do i want ?
I have exact one string with information in it. This string is not sorted or everytime in the same order. I have Keywords and as delimiter the comma, but also amounts in it (Problem to split by comma)

Keywords: AB , BC, CD, DE ( one or all or nothing of it)
delimiter: ,
Example:
AB 123554, BC 123,54, CD H Sven, 22.10.2021, #text to ignore
or
BC 123,54, CD H Sven

i want to split the information into new strings:

Assign:
AB = 123554
BC = 123,54
CD = H Sven
Date = 22.10.2021
or
AB = is empty
BC = 123,54
CD = H Sven
Date = is empty

maybe this is easy for you, but i am struggling.
Thank you very much to help with this.
Jay

[THIRU_NANI (NaNi)]

Hi! @JayLee , welcome to community!

check this out!

How to manipulate a part of string: Split, Trim, Substring, Replace, Remove, Left, Right

Hi, if you are a beginner in the world of automation and would like to know how you can manipulate text based on the solutions available in the VB.NET code and using REGEX (regular expressions) then you are in the right place. In the topic below, you will learn how to easily extract data from any part of the search text using various methods. The topic includes solution examples with descriptions and graphics to better understand the topic for functions such as: Split Substring Left Right R…

Regards,
NaNi

[kumar.varun2 (Varun Kumar)]

Hi @JayLee

This can be done as follows

AB = If(Regex.Match(inputStr, "(?<=AB )[\w\ ]+(?=\,)") is Nothing, "", Regex.Match(inputStr, "(?<=AB )[\w\ ]+(?=\,)").ToString)

BC = If(Regex.Match(inputStr, "(?<=BC )[\w\ ]+(?=\,)") is Nothing, "", Regex.Match(inputStr, "(?<=BC )[\w\ ]+(?=\,)").ToString)

CD = If(Regex.Match(inputStr, "(?<=CD )[\w\ ]+(?=\,)") is Nothing, "", Regex.Match(inputStr, "(?<=CD )[\w\ ]+(?=\,)").ToString)

DE = If(Regex.Match(inputStr, "(?<=DE )[\w\ ]+(?=\,)") is Nothing, "", Regex.Match(inputStr, "(?<=DE )[\w\ ]+(?=\,)").ToString)

Date_ = If(Regex.Match(inputStr, "[0-3][0-9].[0-1][0-9].\d{4}") is Nothing, "", Regex.Match(inputStr, "[0-3][0-9].[0-1][0-9].\d{4}").ToString)

Please find the attached xaml file for your reference

ABBCCDDEDate.xaml (7.3 KB)

[JayLee (Jay)]

Hi @kumar.varun2 ,

thank you for the quick answer to my problem!

I run your .xaml and do a write line , to get the information.
Unfortunately , the BC is not working for me , cause it is cutting the amount at the comma

outcome is perfect except the BC(amount):

AB = 123554, BC ist 123, CD is H Sven, DAte is 22.10.2021, DE is

How can i solve it , to get the full amount of 123,54 and as additional question, , what if there is a currancy behind the amount(can be different and nothing) and i want the currancy too ?
again , a first example with small change to understand the working of regex
AB 1235-54Bd, BC 123,54 USD, CD H Sven, 22.10.2021, #text to ignore

How do i have to change the AB Part , if want to get all the information alphanumerical or let me say , everything i can enter with the keyboard( §$-/±@aB[ } )

is it correct , to write this Date as i do in the month part?
Date_ = If(Regex.Match(inputStr, “[0-3][0-9].[0-1][0-2].\d{4}”) is Nothing, “”, Regex.Match(inputStr, “[0-3][0-9].[0-1][0-2].\d{4}”).ToString)
What if the date is coming only with 2 digits year (31.10.21) - can i use then \d{2] ? and how can i mix both?

Thank you again for your help! I am learning much more better with examples.
Regards,
Jay

[kumar.varun2 (Varun Kumar)]

Hi @JayLee

Can you list down the all possibilities if possible with one example of each and the corresponding output.

[JayLee (Jay)]

Hi @kumar.varun2 ,

i try my best…

You can have all the keywords and the date in different order
eg.
AB 123, CD H Sven, 22.10.21, BC 123,54 USD

Output
AB=123
CD= H Sven
Date= 22.10.21
BC=123,54 USD

eg.
AB 123, BC 123,54 USD, CD H Sven, 22.10.21

Output
AB=123
BC=123,54 USD
CD= H Sven
Date= 22.10.21

AB could have alpha and numerical digits
eg.
AB 123 >outcome AB = 123
AB 123-45 > AB= 123-45
AB 123/45 > AB= 123/45
AB 123ABC/45 > AB= 123ABC/45
AB 123-45/2021 > AB= 123-45/2021

BC is an amount with or without a currency
eg.
BC 123,54 > outcome BC= 123,54
BC 123,54 USD > out BC = 123,54 USD
BC 123,54 INR > out BC = 123,54 INR

CD is always alphabetical with mix of capital letters and small Letters(normally names)
eg
CD H Sven >out CD= H Sven
CD F Gisela >out CD= F Gisela
CD H John Doe > out CD= H John Doe
CD F Jane Doe > out CD= F Jane Doe

The date can vary
eg.
22.10.21 > out Date=22.10.21
22.10.2021 > out Date=22.10.2021
everytime in the order of day,month,year

It is possible , that i get only single information in the string or all of them
eg
BC 123,50 USD, CD H John Doe
CD H John Doe
AB 123ABC/45, CD H John Doe, BC 123,50 USD

…hm, thats a lot…
hope this will help better to understand the target outcome.
Thank you very much for your help
Regards
Jay

[kumar.varun2 (Varun Kumar)]

Hi @JayLee

Find the updated xaml

ABBCCDDEDate.xaml (7.6 KB)

[JayLee (Jay)]

Hi @kumar.varun2

wow! this is exactly , what i am searching for - many thanks for your help!

Now i understand , that i can use | for adding a condition. But what is in the BC the last “,$” ?

Regards and respect
Jay