how to extract with several categories...?

zaidan

New Member
Joined
Sep 18, 2008
Messages
34
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....
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try something like this. (Substitute commas with semicolons).
Excel Workbook
AB
1METAR WARR 120000Z 29006KT 6000 HZ FEW020 25/24 Q1008 NSG=6000
2METAR WARR 120330Z 30007KT 260V340 9999 SCT020 31/23 Q1008=9999
Sheet
 
Upvote 0
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....
Something like...

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

Replace comma with semi-colon for your language version.
 
Upvote 0
cool, it's work fine...

can you help me to solve one more problem...:)

i want to parse data of cloud amount and height.
FYI,
- Cloud amount coded by FEW, SCT, BKN AND OVC
- Cloud height coded by three digit number i.e 020 refer 2000 ft, 030 refer 3000 ft, 040 refer 4000 ft etc.
- rest of the codes are SKC (sky clear), NSC (No Significant Cloud) and CAVOK (Cloud And Visibility OK)

example :


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 537px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">DATA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120000Z 29006KT 6000 HZ FEW020 25/24 Q1008 NSG=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120330Z 30007KT 260V340 9999 SCT030 31/23 Q1008=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120430Z 29005KT 240V330 9999 BKN020 31/24 Q1007=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120030Z 26006KT 7000 HZ OVC020 26/24 Q1009 NSG=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120100Z 27005KT 8000 HZ NSC 27/24 Q1009=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120200Z 28004KT 230V310 8000 HZ SKC 27/24 Q1009=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 061900Z VRB02KT CAVOK 25/25 Q1009</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

result i want is :

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 94px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Cloud Amount</TD><TD style="TEXT-ALIGN: center">Cloud Height</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">FEW</TD><TD style="TEXT-ALIGN: center">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">SCT</TD><TD style="TEXT-ALIGN: center">3000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">BKN</TD><TD style="TEXT-ALIGN: center">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">OVC</TD><TD style="TEXT-ALIGN: center">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">NSC</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">SKC</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">CAVOK</TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

thanks for any help..

sincerely,
zaidan
 
Upvote 0
cool, it's work fine...

can you help me to solve one more problem...:)

i want to parse data of cloud amount and height.
FYI,
- Cloud amount coded by FEW, SCT, BKN AND OVC
- Cloud height coded by three digit number i.e 020 refer 2000 ft, 030 refer 3000 ft, 040 refer 4000 ft etc.
- rest of the codes are SKC (sky clear), NSC (No Significant Cloud) and CAVOK (Cloud And Visibility OK)

example :


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 537px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">DATA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120000Z 29006KT 6000 HZ FEW020 25/24 Q1008 NSG=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120330Z 30007KT 260V340 9999 SCT030 31/23 Q1008=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120430Z 29005KT 240V330 9999 BKN020 31/24 Q1007=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120030Z 26006KT 7000 HZ OVC020 26/24 Q1009 NSG=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120100Z 27005KT 8000 HZ NSC 27/24 Q1009=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 120200Z 28004KT 230V310 8000 HZ SKC 27/24 Q1009=</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 8pt">METAR WARR 061900Z VRB02KT CAVOK 25/25 Q1009</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

result i want is :

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 94px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Cloud Amount</TD><TD style="TEXT-ALIGN: center">Cloud Height</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">FEW</TD><TD style="TEXT-ALIGN: center">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">SCT</TD><TD style="TEXT-ALIGN: center">3000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">BKN</TD><TD style="TEXT-ALIGN: center">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">OVC</TD><TD style="TEXT-ALIGN: center">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">NSC</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">SKC</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">CAVOK</TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

thanks for any help..

sincerely,
zaidan
Create a range housing:
<TABLE style="WIDTH: 77pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=102><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" id=td_post_2683357 class=xl63 height=19 width=102>FEW</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=19 width=102>SCT</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=19 width=102>BKN</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=19 width=102>OVC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=19 width=102>NSC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=19 width=102>SKC</TD></TR>
<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" id=td_post_2683357 class=xl64 height=20 width=102>CAVOK</TD></TR>
</TBODY></TABLE>

and name this range CAList. Also another consisting of:
<TABLE style="WIDTH: 77pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=102><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" id=td_post_2683357 class=xl65 height=19 width=102>NSC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=19 width=102>SKC</TD></TR>


<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" id=td_post_2683357 class=xl66 height=20 width=102>CAVOK</TD></TR>
</TBODY></TABLE>

Name this range CASubList.

Now we can invoke...

B2, just enter & copy down:
Rich (BB code):
=LOOKUP(9.99999999999999E+307,SEARCH(" "&CAList,A2),CAList)

C2, just enter & copy down:
Rich (BB code):
=IF(ISNUMBER(MATCH(B2,{"NSC";"SKC";"CAVOK"},0)),"",
     MID(A2,SEARCH(B2,A2)+3,3)*100)
 
Upvote 0
Thank's a lot it's work fine with a little bit adjusment to my xl.
I hope you can help me further if i have another difficulty wit xl.
 
Upvote 0
Be aware zaidan that a METAR often has more than one cloud level reported. Aladin's fine formula will extract just One of the Cloud Density terms and it's not necessarily the first one in the METAR.

I just wanted to make sure you're not making any flight planning decisions based on incomplete Wx data.
 
Upvote 0
Thank's a lot it's work fine with a little bit adjusment to my xl.
I hope you can help me further if i have another difficulty wit xl.

Great. Thanks for providing feedback.

By the way, the {"NSC";"SKC";"CAVOK"} bit can be replaced with CASubList.
 
Upvote 0
@alphafrog : no, i'm not making any flight planning decisions, i'm just collecting and processing METAR data..

I just realize that CAVOK also refer to Visibility.
how can i extract CAVOK and put it into Visibility column ?
since the formula above return #VALUE! in the visibility column.
 
Upvote 0
@alphafrog : no, i'm not making any flight planning decisions, i'm just collecting and processing METAR data..

I just realize that CAVOK also refer to Visibility.
how can i extract CAVOK and put it into Visibility column ?
since the formula above return #VALUE! in the visibility column.

How do you mean?

If you don't use the named lists....

B2, copy down:
Code:
=LOOKUP(9.99999999999999E+307,
    SEARCH(" "&{"FEW";"SCT";"BKN";"OVC";"NSC";"SKC";"CAVOK"},A2),
      {"FEW";"SCT";"BKN";"OVC";"NSC";"SKC";"CAVOK"})

C2, copy down:
Code:
=IF(ISNUMBER(MATCH(B2,{"NSC";"SKC";"CAVOK"},0)),"",
     MID(A2,SEARCH(B2,A2)+3,3)*100)

These formulas (also the ones with the named lists should not return a #VALUE! error...
 
Upvote 0

Forum statistics

Threads
1,215,949
Messages
6,127,880
Members
449,411
Latest member
AppellatePerson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top