Extracting Data

[PTori82]

I am trying to extra data from a cell and then take the extracted data and place it in a new cell. Here is an example: Cell F5 reads "SCT021 BKN035 OVC070". Cell X5 reads "CLR" and cell Y5 reads "CLR". Cells X5 and Y5 read clear because I have the following formulas: In cell X5 is the formula: =IF(OR(LEFT(F5,3)="BKN",LEFT(F5,3)="OVC"),F5,"CLR"). Cell Y5 has the formula: =RIGHT(X5,3).

The intent of cell X5 is to extract the values from a cell if it contains the words "BKN" or "OVC". So if cell F5 reads "SCT021 BKN035 OVC070" cell X5 reads "BKN035 OVC070". The intent of Y5 is to extract just the numerical value so in this example, Y5 would read "035 070". This isn't working for me when cell F5 has more than one value in the cell.

The ultimate goal is to get ALL the values in cell F5 that have the prefix of BKN or OVC to display in cell X5. And then in cell Y5, I want the lowest numerical to be displayed without any text. Example: Cell F5 reads "SCT021 BKN035 OVC070", cell X5 reads "BKN035 OVC070" and cell Y5 reads "035". Here's the kicker, if cell F5 does not have "BKN" or "OVC" then I want cell X5 and Y5 to read "CLR". Furthermore, Cell F5 can have up to 5 values entered into it. So in the example I keep using, "SCT021 BKN035 OVC070", there could be two additional values. In the end, whether there is 1 value or 5 values in F5, All I want in lowest numerical value affixed to "BKN" or "OVC" to be displayed in a separate cell.

I greatly appreciate any help on this. So far, this forum has been the most helpful of ANY type of forum I have written in. Thank you!

 

[RonB1111]

This single formula gives the lowest numerical value affixed to BKN or OVC and if none exist, it returns "CLR". You didn't state it in your post, but I've assumed there will not be multiple BKN's or multiple OVC's in a single cell - if there are, then this formula only looks at the 1st instance of each.

=IFERROR(MIN(IFERROR(MID(F5,SEARCH("BKN",F5)+3,3),IFERROR(MID(F5,SEARCH("OVC",F5)+3,3),"CLR"))),"CLR")

 

[PTori82]

Thanks, Ron! That worked perfectly. The only thing I need to fix is that when a value is reported such as BKN028, the result is 28 as opposed to 028. In some instances, such as BKN170 the return is 170, which is fine since the value always has to be 3 numbers. How would I get a value with a zero in front, such as BKN028 to display 028 as opposed to 28 while keeping values with no zero in front to still display, such as BKN170 to 170?

 

[PTori82]

Disregard, I totaly forgot that I could custom format the cell for 3 zero's.

 

[RonB1111]

You're welcome. Thank you for providing feedback.