Contain Formula

Devika

Hello,

I wanted to separate and classify the data according to the below input and output

the trade ref that contain D is the domestic trades and trade ref that contains I is international

INPUT

Trade Refs (Ours)

D345447 (000005)

D343792 (000005)

D527015 (002884)

D520288 (002884)

I129702 (000005)

D418531 (000005), D420345

D428163 (000005), D428920

D402199 (083004), D523515(083004), D317365 (083004),D325464 (083004), D419502(000005), D525641 (000005)

Output

Trade Refs (Ours)	Dom/Intl
D345447 (000005)	Domestic
D343792 (000005)	Domestic
D527015 (002884)	Domestic
D520288 (002884)	Domestic
D520288 (002884)	Domestic
I129702 (000005)	Intl
I129702 (000005)	Intl
D418531 (000005), D420345	Domestic
D428163 (000005), D428920	Domestic
D402199 (083004), D523515(083004), D317365 (083004),D325464 (083004), D419502(000005), D525641 (000005)	Domestic

Tips and Tricks

Accepted answers

binu_acs

@Devika

IIF(Left([Trade Refs (Ours)     ], 1) == 'D', 'Domestic', 'Intl')

Domestic.yxmd

All comments

grchavarri

Hi @Devika

You can use an if in the formula tool including the function Contains

ie.

if Contains(Trade Refs (Ours),"(000005)") then "Intl" else "Domestic" endif

Hope it helps.

Best,

Kenda

Hi @Devika

Here you can use a formula tool to create a new field. Based on the title of this post, it seems like you are close. The contains formula first needs to know what string you're looking in, then what you're looking for. Something like this:

contains([Trade Refs (Ours)], "D")

To create the output field you want, try wrapping this in an if statement!

binu_acs

@Devika

IIF(Left([Trade Refs (Ours)     ], 1) == 'D', 'Domestic', 'Intl')

Domestic.yxmd

Kenda

Alternatively, if the first character of your field is always going to be either I or D, you could try the substring function:

substring([Trade Refs (Ours)],0,1)

This looks at the string you specify then it says start at the 0th character and keep only 1 character. To get your new field, just check if this is equal to D or I.

MarqueeCrew

I like @Kenda alternatives so I'll add one:

IIF(StartsWith([Trade Refs (Ours)], "D"),"Domestic","International")

That was fun 🤣. @Devika , I hope that you enjoy the variety of solutions presented here.

Cheers,

Mark

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335