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:
<tbody>
</tbody>
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:
<tbody>
</tbody>
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.
The string consists of a date (mm/dd/yyyy) and either 1 of two words: Yes or No.
Example:
| 07/22/2015 - No |
<tbody>
</tbody>
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:
| 05/05/2015 - Yes |
| 05/05/2015 - Yes |
| 07/22/2015 - No |
| 09/23/2015 - No |
| 10/26/2015 - No |
| 05/26/2016 - Yes |
| 09/21/2016 - No |
| 10/13/2018 - Yes |
<tbody>
</tbody>
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.
Last edited:
