Return a specific text within a string

[DevinSuth]

I am in need of assistance with this one.

I have multiple cells that contain different sets of data...
A B C D
OVC005 FEW030 SCT075 BKN150 BKN020 BKN075 OVC150 BKN015 BKN075 OVC120

I Need a formula to return the text behind "BKN" or "OVC" as seen below....and if that's not complicated enough I only need it to return those 3 number if it is "015" or less.

A B C D
005 150 020 015

so A I would need "005" B and C would be blank and D would return the value of "015".

Ideas??? Formulas....please help!!!!

 

[DevinSuth]

A
ovc005

b
few030 sct075 bkn150

c
bkn020 bkn075 ovc150

d
bkn015 bkn075 ovc012

 

[Rick Rothstein]

I am in need of assistance with this one.

I have multiple cells that contain different sets of data...
A B C D
OVC005 FEW030 SCT075 BKN150 BKN020 BKN075 OVC150 BKN015 BKN075 OVC120

I Need a formula to return the text behind "BKN" or "OVC" as seen below....and if that's not complicated enough I only need it to return those 3 number if it is "015" or less.

A B C D
005 150 020 015

so A I would need "005" B and C would be blank and D would return the value of "015".

I am sorry, but your instructions are not clear. You only want 4 values if what is "015" or less?

What should happen if the value is greater than "015"?

Also, why did you skip BKN075 that is located between BKN020 and OVC150?

What do you mean that B and C would be blank... you show values in them?

 

[DevinSuth]

If I have a cell that has

BKN015 BKN075 BKN012

I only need it to send me the "015"

If I have a cell that has

SCT005 BKN010 BKN030

I need it to display "010"

 

[DevinSuth]

this is for sky condition, I will have SKC FEW SCT BKN OVC
I only need the last 3 digits after BKN and OVC if the number is "015" or less

 

[Rick Rothstein]

If I have a cell that has

BKN015 BKN075 BKN012

I only need it to send me the "015"

Why not also the "012" from the highlighted text... isn't it less than "015"?

 

[DevinSuth]

that was a typo on my part

the way the data is input into the system its always from lowest to hightest

it should have been BKN120

so SCT005 SCT010 BKN012 OVC015
I would need 012

 

[DevinSuth]

anyone have any ideas???

 

[DevinSuth]

=iferror(iferror(if(right(mid(d$10,find("bkn",d$10,1),6),3)<"016",right(mid(d$10,find("bkn",d$10,1),6),3),if(right(mid(d$10,find("ovc",d$10,1),6),3)<"016",right(mid(d$10,find("ovc",d$10,1),6),3),"")),if(right(mid(d$10,find("ovc",d$10,1),6),3)<"016",right(mid(d$10,find("ovc",d$10,1),6),3),"")),"")

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(TEXT(1/(1/MIN(IF(ISNUMBER(MATCH(MID(A2:D2,1,3),{"BKN","OVC"},0)),REPLACE(A2:D2,1,3,"")+0))),"000"),"not available")

If the items are not in cells of their own as assumed here, just dismiss this suggestion.