IF Contains Function in Formula

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

Datasets

Data Investigation

Accepted answers

All comments

binu_acs

@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.

binu_acs

@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....

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703