Search for certain string

[Ryan]

Hello, trying to figure out how to search for some text. What i Have
is some multiple text data on line a2 to a9 , what i need to do is
search each line and find the first instance of the letters BKN or OVC
then take the next three digits after that and place it into the
corresponding row B, (for ex. if a2 is bkn011 is in line a2 then b2
is 011). Then if there is no BKN or OVC i need to look for FEW or SCT
and then put the either the FEW or SCT along with the three digitsin
parenthesis. ( for ex if a2 is sct033 then be will be (sct033). Any
help would be appreciated. Thank You.

 

[Bernie Deitrick]

Ryan,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim myCell As Range

Set myCell = Columns("A:A").Find(What:="BKN", _
LookAt:=xlPart)
If Not myCell Is Nothing Then
myCell(1, 2).Value = Mid(myCell.Value, _
InStr(1, myCell.Value, "BKN") + 3, 3)
Exit Sub
End If
Set myCell = Columns("A:A").Find(What:="OVC", _
LookAt:=xlPart)
If Not myCell Is Nothing Then
myCell(1, 2).Value = Mid(myCell.Value, _
InStr(1, myCell.Value, "OVC") + 3, 3)
Exit Sub
End If
Set myCell = Columns("A:A").Find(What:="FEW", _
LookAt:=xlPart)
If Not myCell Is Nothing Then
myCell(1, 2).Value = "(" & Mid(myCell.Value, _
InStr(1, myCell.Value, "FEW"), 6) & ")"
Exit Sub
End If
Set myCell = Columns("A:A").Find(What:="SCT", _
LookAt:=xlPart)
If Not myCell Is Nothing Then
myCell(1, 2).Value = "(" & Mid(myCell.Value, _
InStr(1, myCell.Value, "SCT"), 6) & ")"
Exit Sub
End If
End Sub