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)

D520288 (002884)

I129702 (000005)

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

[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!

[binuacs · Accepted solution]

@Devika 

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

[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