Calculated column to search and return multiple strings

[jtpryan]

So I have a column SUMMARY and a calculated column ISSUE. in the ISSUE column I have a DAX formula:

=IF(FIND("SSH",[Summary],1,BLANK()),"SSH",BLANK())

So each time it finds a string "SSH" in SUMMARY, it puts the string SSH in ISSUE. This works fine.

What I would like to be able to do is have it look for multiple strings and do the same thing. So if it finds XXX it puts XXX in ISSUE. If it finds YYY it puts YYY in ISSUE.

Pseudo would be (IF find "AAA" in SUMMARY then write "AAA" in ISSUE) OR (IF find "BBB" in SUMMARY then write "BBB" in ISSUE) OR (IF find "CCC" in SUMMARY then write "CCC" in ISSUE)

So in my calculated column I would have
AAA
AAA
BBB
AAA
CCC

And, of course, if none of them found, blanks.

Any help appreciated.

Jim

 

[MrIfOnly]

Hi Jim,

Is this what you want? A2 would be whatever your summary column is...

=IF(ISERROR(FIND("SSH",A2,1)),IF(ISERROR(FIND("XXX",A2,1)),IF(ISERROR(FIND("YYY",A2,1)),"","YYY"),"XXX"),"SSH")<strike></strike>

Regards,

CJ

 

[jtpryan]

CJ,

Hey, thanks, that worked well. I do have one question though. Nested expressions like this are incredibly hard for me to follow, guess I don't have the wiring for it. Is there a trick to how to follow it? I mean, if I wanted to continue to add more strings to search for I seem to just get into trouble. Any tips would be appreciated.

Jim.

 

[MrIfOnly]

Hi Jim:

Sorry it took so long for me to reply. Take a look at this thread: thread particularly Aladin's post #2 and my post #10 for some techniques on breaking long nested if formulas down.

If you still get stuck feel free to post back.

Regards,

CJ