Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

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

cancel
Showing results for 
Search instead for 
Did you mean: 
SharonC
5 - Automation Enthusiast
5 - Automation Enthusiast
Status: New Ideas

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 Comments
TheTimeSavingCo
18 - Pluto
18 - Pluto

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

TheTimeSavingCo
18 - Pluto
18 - Pluto

@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!

 

 

 

TheTimeSavingCo
18 - Pluto
18 - Pluto

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