How To Parse Specific text from String Dat

[zaidan]

I have set of data in one column contain spesific code of meteorological element, but each data does not have same format, what i want to do is to get some text(data) by parsing from the code and put them in different column with certain category.

Example :
data: (in column A)
1 METAR WARR 120000Z 29006KT 6000 HZ FEW020 25/24 Q1008 NSG=
2 METAR WARR 120100Z 00000KT 7000 SCT020 25/24 Q1008=
3 METAR WARR 120130Z 28004KT 240V300 8000 HZ NSC 27/24 Q1009=
4 METAR WARR 121230Z 00000KT 7000 RA FEW020CB SCT020 24/23 Q1008=

the result i expect :
____B______C______D______E______F______G_____H_____I______J____
_direction_speed_Visibility_Weather_amount_height_temp_D.point_Pressure
1__290_____06___6000_____HZ____FEW___2000___25____24_____1008
2__000_____00___7000___________SCT____2000___25____24_____1008
3__280_____04___8000_____HZ__________________27____24_____1009
4__000_____00___7000_____RA____SCT____2000___24____23_____1008


explaination for row 1:
- Wind direction and speed get from : 29006KT (direction 290 , speed 06)
- Visibility get from : 6000
- Weather get from : HZ (row 2 weather is empty)
- Cloud Amount & Height get from : FEW020 (amount FEW , height 20 => 2000) (row 3 cloud amount & height is empty)
- Temperature & Dewpoint get from : 25/24 (temperature 25, D.point 24)
- Pressure get from : Q1008

Thanks for any help

 

[texasalynn]

Ok I got everything working except for one visibility item (3rd line). Also I'm wondering if you want to keep the data in column A?

Sub Wind_Parse()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
SC = ActiveCell.Row
' parse out WIND
For i = LR To SC Step -1
    MCC = WorksheetFunction.Find("Q", Cells(i, 1)) + 1
    MCC1 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, "/", "|")) + 1
    xx = IsError(Application.Find("SCT", Cells(i, 1)))
    If xx <> True Then
        MCC2 = Application.Find("SCT", Cells(i, 1))
    End If
    If xx = True Then
        MCC2 = Application.Find("FEW", Cells(i, 1))
    End If
    MCC3 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, " ", "|", 3)) + 1
    xx = IsError(Application.Find("HZ", Cells(i, 1)))
    If xx <> True Then
        MCC4 = Application.Find("HZ", Cells(i, 1))
    End If
    If xx = True Then
        MCC4 = Application.Find("RA", Cells(i, 1))
    End If
    MCC5 = Application.WorksheetFunction.Find("|", Application.WorksheetFunction.Substitute(Cells(i, 1).Value, "KT", "|")) + 3
    Cells(i, 10) = Mid(Cells(i, 1), MCC, 4)
    Cells(i, 9) = Mid(Cells(i, 1), MCC1, 2)
    Cells(i, 8) = Mid(Cells(i, 1), MCC1 - 3, 2)
    If IsError(MCC2) <> True Then
        Cells(i, 7) = Mid(Cells(i, 1), MCC2 + 4, 2) * 1000
        Cells(i, 6) = Mid(Cells(i, 1), MCC2, 3)
    End If
    If IsError(MCC4) <> True Then
        Cells(i, 5) = Mid(Cells(i, 1), MCC4, 2)
    End If
    Cells(i, 4) = Mid(Cells(i, 1), MCC5, 4)
    Cells(i, 3).NumberFormat = "@"
    Cells(i, 3) = Mid(Cells(i, 1), MCC3 + 3, 2)
    Cells(i, 2) = Mid(Cells(i, 1), MCC3, 3)
    
Next i
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[zaidan]

Thanks alynn for your help,

But i have another problem :
- Cloud amount are not only FEW and SCT, there are also BKN and OVC.
- Weather are not only HZ and RA, there are also many moore such as DR, BR, TSRA, etc.
- Visiblity value are from 1000 to 9000 metres, but if visibility are over then 10 kms visibility coded with 9999.

so alynn can you help me by making the VBA that i can customize...
(i'm a newbie in VBA )

 

[Leith Ross]

Hello zaidan,

The more complete the your list of abbreviations is for each column, the easier it will be to write code to parse the data correctly.

Sincerely,
Leith Ross