Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for certain string
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for certain string
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 "Ryan" wrote in message om... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for a string | New Users to Excel | |||
Search for string containing | Excel Discussion (Misc queries) | |||
How to search a string from the right ? | Setting up and Configuration of Excel | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Q: search in string | Excel Discussion (Misc queries) |