Skip to main content

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!

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

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.
 
 

How about using Regex?

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

In this case, return the status of the first match

@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

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!

 

 

 

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",

 

Reply


Terms & Conditions