Help

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

741 6
cancel
Showing results for 
Search instead for 
Did you mean: 
SharonC
5 - Automation Enthusiast
5 - Automation Enthusiast

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)

SharonC_0-1710927698701.png

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!

6 Replies 6

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
10 - Mercury
10 - Mercury
IF(
     FIND("Ongoing "&Name, {Updated Status}), "Ongoing",
     IF(
          FIND("Hold "&Name, {Updated Status}), "Hold",
          IF(
               FIND("Cancelled "&Name, {Updated Status}), "Cancelled"
          )
     )
)
Screenshot 2024-03-20 at 1.09.46 PM.png
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
11 - Venus
11 - Venus

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

SharonC
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

SharonC_2-1711014634251.png

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