Search and Match string values in new column

[Kishore_MJ]

Hello,

I have the below scenario, Column A(Unique Identifier) and Column B (String)

In column C(Expected column)

1. IF Column B contains "Video platform" then Column C = Video Platform
2. IF Column B= Null then Column C = Null
3. If Column B contains Audio platform then Column C =  Audio Platform
4. If Column B Not equal to 'Video or Audio platform' then column C = Others
5. If Column B contains both "Video platform" and " Audio platform"(Any sequence) then column c = ' Video and Audio platform'

I tried with formula tool but I could not able to get the desire value in C.

Column A	Column B	Column C
Unique Identifier	String	Expected column
1	Video platform, other System	Video Platform
2	Null	Null
3	Audio Platform	Audio Platform
4	others System	others
5	Video platform, other system, Audio Platform	Video and Audio platform

[apathetichell]

Your problem is your order of operations is wrong.

1. IF Column B contains "Video platform" then Column C = Video Platform
2. IF Column B= Null then Column C = Null
3. If Column B contains Audio platform then Column C =  Audio Platform
4. If Column B Not equal to 'Video or Audio platform' then column C else= Others
5. If Column B contains both "Video platform" and " Audio platform"(Any sequence) then column c = ' Video and Audio platform'

Put choice 5 first.

[Carolyn · Accepted solution]

Give this a try:

if Contains([Column B], "Video platform") and Contains([Column B], "Audio platform")
then "Video and Audio Platform"

elseif Contains([Column B], "Video platform")
then "Video Platform"

elseif Contains([Column B], "Audio platform")
then "Audio Platform"

elseif isnull([Column B])
then null()

else "Others"
endif