Find first instance of text in range of cells

[spydey]

I have a range. The cells contain a string in them.

The string consists of a date (mm/dd/yyyy) and either 1 of two words: Yes or No.

Example: [TABLE="width: 500"]
<tbody>[TR]
[TD]07/22/2015 - No[/TD]
[/TR]
</tbody>[/TABLE]

They are in a single cell, in a single column.

Regardless of I need to extract the date preceding the " - " from the first instance where the word is No.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]05/05/2015 - Yes[/TD]
[/TR]
[TR]
[TD]05/05/2015 - Yes[/TD]
[/TR]
[TR]
[TD]07/22/2015 - No[/TD]
[/TR]
[TR]
[TD]09/23/2015 - No[/TD]
[/TR]
[TR]
[TD]10/26/2015 - No[/TD]
[/TR]
[TR]
[TD]05/26/2016 - Yes[/TD]
[/TR]
[TR]
[TD]09/21/2016 - No[/TD]
[/TR]
[TR]
[TD]10/13/2018 - Yes[/TD]
[/TR]
</tbody>[/TABLE]

Regardless of how many Yes are before or after, regardless of how many No are after, I need the date associated with the first No in the list.

From my example list above, I would need the date of: 07/22/2015, because it is the date of the first iteration of the text "No".

I hope that makes sense.

I have tried multiple things, but nothing is getting me exactly what I need.

It would be so much easier if they were separated into individual columns (which I could do via Text to Columns), but I don't have the option to do that with this data set.

Any thoughts?

A formula or some VBA code to point me in the right direction would be greatly appreciated.

-Spydey

P.S. If I could simply isolate which cell the first iteration is in, then I could do a simple Left function and number of characters over to capture the date.

But isolating which cell the first iteration of "No" is in, is proving to be the difficult aspect. I believe primarily due to how I have been attempting it.

 

[Marcelo Branco]

Maybe...

=LEFT(INDEX(A1:A8,MATCH("*NO*",A1:A8,0)),10)

If you want a real date (number)
=--LEFT(INDEX(A1:A8,MATCH("*NO*",A1:A8,0)),10)

M.

 

[spydey]

@ Marcela Branco

Thanks! I am going to give this an attempt.

-Spydey

 

[spydey]

Maybe...

=LEFT(INDEX(A1:A8,MATCH("*NO*",A1:A8,0)),10)

If you want a real date (number)
=--LEFT(INDEX(A1:A8,MATCH("*NO*",A1:A8,0)),10)

M.

Hmmm, so I tried it and it isn't working. I tried it as a simple formula ... didn't work.

I tried it via CSE for an array formula. No go either.

I filled it down and still no results.

I think that we might be on the right path but that it needs a bit of tweaking.

-Spydey

 

[spydey]

@ Marcelo Branco

I feel like such an moron.

I was entering the wrong word .... I apologize.

Your formula work great!!!

Thank you!!

-Spydey

 

[Marcelo Branco]

You are welcome. Glad to help.

M.