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

709 0
cancel
Showing results for
Did you mean:
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)

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

10 - Mercury
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.

11 - Venus

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

In this case, return the status of the first match

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

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?

Thanks again!

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