How To Parse Specific text from String Dat

Z

zaidan

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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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?

Code: 
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
 
Upvote 0
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 :) )
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Locked
  • Question Question
How To Parse Specific text from String Data
Replies
1
Views
651
Peter_SSs
Peter_SSs
Z
  • Question Question
how to extract with several categories...?
2
Replies
11
Views
4K
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

Share this page

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