how to extract with several categories...?

[zaidan]

I want to extract visibility data from a meteorological raw data that contain 1000 to 9000 and coded 9999 if the visibility 10000 or above.

example :
METAR WARR 120000Z 29006KT 6000 HZ FEW020 25/24 Q1008 NSG=
to extract data 1000 to 9000 i use : =MID(A1;FIND("000 ";A1)-1;4)
and the result is 6000
METAR WARR 120330Z 30007KT 260V340 9999 SCT020 31/23 Q1008=
to extract data 9999 i use : =MID(A2;FIND("9999";A2);4)
the result is 9999

Can anyone help me How to combine those two formula in single formula?

thanks in advance....

 

[zaidan]

sorry aladin for troubling you...

The formula you gave me work just fine for cloud amount and height..

Since CAVOK is Cloud And Visibility OK. so CAVOK refer to cloud and Visibility.

So what i ment is that if the Data contain CAVOK it's return CAVOK to visibility column (as it return CAVOK to Cloud Amount),
instead 1000, 2000, ...9000 or 9999 as the formula in your first reply.

thanks for your reply,

 

[Aladin Akyurek]

sorry aladin for troubling you...

The formula you gave me work just fine for cloud amount and height..

Since CAVOK is Cloud And Visibility OK. so CAVOK refer to cloud and Visibility.

So what i ment is that if the Data contain CAVOK it's return CAVOK to visibility column (as it return CAVOK to Cloud Amount),
instead 1000, 2000, ...9000 or 9999 as the formula in your first reply.

thanks for your reply,

After additional clarifications (via a PM)...

=IF(ISNUMBER(SEARCH(" cavok",A1)),"CAVOK",
   IF(ISNUMBER(FIND(" 9999 ",A1)),"9999",MID(A1,FIND("000 ",A1)-1,4)))