Formula to extract 1st word if condition met in multiple response column

[SharonC]

Hi All

I am looking for a formula to extract project status from a string with multiple response column. Criteria is IF "Market Single" is appear anywhere in the "Updated Status", then return as the 1st word in that particular string. 

Example below: the output I am looking for in "Final Status" is:

- Row 1: Ongoing (Albania in Market single, Ongoing being the first word in the string)

- Row 2: Hold (Andorra in Market single, Hold being the first word in the string)

- Row 3: Cancelled (Antigua in Market single, Cancelled being the first word in the string)

Currently this is the formula I have - and it returns all three response as Ongoing - which is not what I am looking for:

IF(

  FIND({Market single}, {Updated Status})>0,

  LEFT({Updated Status},FIND(" ",{Updated Status}) - 1),

  ""

)

Thanks a mil!

[TheTimeSavingCo]

Hmm the only way I can think of doing this is via a script I'm afraid.  Hopefully someone else has a better idea!

[pressGO_design]

IF(

     FIND("Ongoing "&Name, {Updated Status}), "Ongoing",

     IF(

          FIND("Hold "&Name, {Updated Status}), "Hold",

          IF(

               FIND("Cancelled "&Name, {Updated Status}), "Cancelled"

          )

     )

)

Editing this to add a caveat: it will only work if you have only one iteration of the Name in the Updated Status field. If you have something like Ongoing Country A and Cancelled Country A in Updated Status, this formula will return only the first iteration.

[Sho]

How about using Regex?

REGEX_EXTRACT({Updated Status}, "([^, ]*) " & {Market single})

In this case, return the status of the first match

[TheTimeSavingCo]

@pressGO_designOooh, what if you moved the "IF"s out and just did a "IF() & IF()" instead of nesting?  That way it'd output all the iterations that matched the name?

Man if there was a kudos button I'd like both yours and @Sho 's posts; they're so elegant

[SharonC]

Thanks so much for the help @pressGO_design @Sho @TheTimeSavingCo 

I realized that the database is not as "clean" - some of the strings has an additional space in between, and so the calculation could not work. Possible to address this from the formula itself?

Thanks again!

[TheTimeSavingCo]

Hmm, utilizing @pressGO_design 's solution, perhaps you could use a 'SUBSTITUTE' to replace the '  ' values with ' ' instead?  i.e.

 FIND("Ongoing "&Name, SUBSTITUTE({Updated Status}, "  ", " ")), "Ongoing",