IF Contains Function in Formula

[Gökhan]

Hi folks,

I have IDs in one column which I want to search for in another column. The problem is that in the search column in the cells before and after there are other characters at irregular intervals. I have therefore entered an IF Contains statement in the formula tool using ChatGpt:

IF Contains([Transaction Reference Number], [Trades]) THEN 'Match' ELSE 'No Match' ENDIF

The list is several thousand lines long, so I need an automated solution method.

For Example i want to match Trade: 5185X27759

Transaction Reference Number: 040010000000000005185X27759INM00549300TPJCLC0OHGM008

Many thanks in advance

[binuacs]

@Gökhan @Have you tried the Find and Replace tool?

[Gökhan]

Hi binuacs Thanks for your respond, not yet.

Is this also suitable if you have the column to be searched for with the target column on an Excel sheet? Or does it have to be two spreadsheets?

[nagakavyasri]

@Gökhan It can be from same excel sheet or different sheets.

[binuacs]

@Gökhan If both the fields are in same file you can use the contains() function (you mentioned above), it should work, if the reference number and the Trade fields are in difference excel sheet then you can go with the find and replace tool with the append output option

[Gökhan]

Hi, unfortunately, that doesn't quite work.

[cjaneczko]

What Binuacs posted should work. I just tested it as well and it works fine. Are your TA and Transaction fields both the same type, ie. V_WString? This could be the issue you are having. Your screen grab shows no for that test, which is correct in your example. 1223X1  16 doesnt exist in the string to your left from what it shows. 

[apathetichell]

@Gökhan  - I'm confused  - your screengrab shows two entities which do not match. No is the expected output. If you are looking for a substring you may need to use regex_match or something to find it....