Hi All, I am seeking assistance in string search, I found a post on here that used 'Getword' but I couldn't make it work. My requirement: I want to search the column 'Include entries' for the value that is in the 'User ID' field, when it finds a matching string, I would like to add a column then simply says "INC" so I can carry this particular record forward in the workflow. User IDInclude entriesimsabc321,BBB321imsabc321,BBB321imsims,abc321,BBB321imsims,abc321,BBB321 I did try the following formula but it only seemed to work if I have an actual matching value, as soon as I expanded the string to include more than one entry in the Include entries field, I got empty responses. IF Getword([Include entries],0) = [User ID] THEN "INC" ELSEIF GetWord([Include entries], CountWords([Include entries])-1) = [User ID] THEN "INC" ELSE "" endif Can anyone offer a solution, I would like to try and avoid breaking the 'Include entries' down in to separate columns as there can be up to 100 records entered!

Use a field value to search in another string and output an inclusion

Hi All,

I am seeking assistance in string search, I found a post on here that used 'Getword' but I couldn't make it work. My requirement:

I want to search the column 'Include entries' for the value that is in the 'User ID' field, when it finds a matching string, I would like to add a column then simply says "INC" so I can carry this particular record forward in the workflow.

User ID	Include entries
ims	abc321,BBB321
ims	abc321,BBB321
ims	ims,abc321,BBB321
ims	ims,abc321,BBB321

I did try the following formula but it only seemed to work if I have an actual matching value, as soon as I expanded the string to include more than one entry in the Include entries field, I got empty responses.

IF Getword([Include entries],0) = [User ID] THEN "INC" ELSEIF
GetWord([Include entries], CountWords([Include entries])-1) = [User ID] THEN "INC"
ELSE "" endif

Can anyone offer a solution, I would like to try and avoid breaking the 'Include entries' down in to separate columns as there can be up to 100 records entered!

Datasets

Data Investigation

Accepted answers

apathetichell

Your issue with the getword strategy is that your [Includes fields] isn't broken up into separate words. You can match via a left([include entries],3) - if it's always the first 3 characters.

If you want to use getword you can add a formula like this to the formula tool and include it prior to (higher up) than your current formula:

replace([Include items],",",", ")

This will allow your getwords formula to work. note - you could put use blankspace in lieu of comma.

All comments

Luke_C

Hi @Bigmonki

The contains function should do the trick for this unless I'm misunderstanding

if contains([Include entries],[User ID]) then 'INC' else null() endif

L_T

Hi, have you tried to use the contains function?

IF contains([Include entries], [User ID]) THEN "INC" ELSE Null() ENDIF

It seems to work with your examples.

apathetichell

Your issue with the getword strategy is that your [Includes fields] isn't broken up into separate words. You can match via a left([include entries],3) - if it's always the first 3 characters.

If you want to use getword you can add a formula like this to the formula tool and include it prior to (higher up) than your current formula:

replace([Include items],",",", ")

This will allow your getwords formula to work. note - you could put use blankspace in lieu of comma.

Bigmonki

Hi,

Didn't realize I could use field as a target, thanks for that. It has kind of worked, but as it is using contain, I am getting false positives where there are User ID's that have ims as part of the string

Bigmonki

@Luke_C @L_T many thanks for the advice, at least now I understand I can use a field as a target for the data. Should have been obvious, but sometimes you need that outside view.

@apathetichell as soon as I removed the comma it worked perfectly, the comma space didn't provide me with any response, as soon as I dispensed with the comma it worked.

Problem solved and I can't thank you all enough for your assistance.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703