String manipulation for excel

Help Studio
,
1

I have a message eg. Bank of ABC, Building KLM, Block 35, India. And I have an excel with below values

ID Name
100 XYZ
100 AB3C Bank
100 Bank of ABC
100 MB

I want to find if the bank name is present in the above message. So I converted the message as below by removing spaces/special characters/newlines:
BANKOFABCBUILDINGKLMBLOCKf35INDIA.

And I also did same with the names and iterated through a loop.

so my condition was:
Message.Contains(Name)

So it checks and provide value as below

  1. BANKOFABCBUILDINGKLMBLOCKf35INDIA.Contains (XYZ) -----------> False
  2. BANKOFABCBUILDINGKLMBLOCKf35INDIA.Contains(AB3CBANK) -----------> False
  3. BANKOFABCBUILDINGKLMBLOCKf35INDIA.Contains(BANKOFABC) -----------> True
  4. BANKOFABCBUILDINGKLMBLOCKf35INDIA.Contains(MB) -----------> True

So the 1, 2 and 3 are giving correct answers but the last (4th) should be “False” instead it is giving “True”. There is MB in it but it is not the proper word in the actual message(" Bank of ABC, Building KLM, Block 35, India")

Is there any other way to solve this??

2

Hi @priyay

You don’t have to remove the spaces & newlines to achieve the above-mentioned result. As you already pointed out the obvious that one data combined with another data is providing the wrong result, you can simply use this RegEx pattern to determine whether the given data is present or not in the input data:

pattern = "\b"+yourKeyword+"\b"

The visual representation of how this works:

image

image

Edit: The expression in studio:

image

Hope this gives you an idea!
Best Regards.

3

Hi @priyay

You can use this as Condition

System.Text.RegularExpressions.Regex.IsMatch(Row("Name").ToString.Trim."Bank of "+BankName,System.Text.RegularExpressions.RegexOptions.IgnoreCase)

In the BankName variable provide one the Bank Name like “ABC”

Regards
Sudharsan

Regards
Sudharsan