Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the best way to query a range for a substring?
I can't seem to find a function to find cells with a particular portion of a
string in them (much like a SQL query using LIKE). Is there such a function? If there is no such function then what would be the most efficient way to do this? I need to search a colum for several different values and then move 1 cell to the right and grab the value there. So fo example: SAU #1 - test 12 SAU #2 - development 25 SAU #3 - specs 12 So in the example I need the values of 12,25,12 but for the text in the first column the only known part that will be there is the first part (i.e. "SAU #1 -") Can Column.Find use a wildcard? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the best way to query a range for a substring?
Sub kemikals()
Dim s As String, v As String s = "SAU #1 -" n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n v = Cells(i, 1).Value If InStr(v, s) 0 Then MsgBox (Cells(i, 2).Value) End If Next End Sub -- Gary''s Student - gsnu200849 "chemicals" wrote: I can't seem to find a function to find cells with a particular portion of a string in them (much like a SQL query using LIKE). Is there such a function? If there is no such function then what would be the most efficient way to do this? I need to search a colum for several different values and then move 1 cell to the right and grab the value there. So fo example: SAU #1 - test 12 SAU #2 - development 25 SAU #3 - specs 12 So in the example I need the values of 12,25,12 but for the text in the first column the only known part that will be there is the first part (i.e. "SAU #1 -") Can Column.Find use a wildcard? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the best way to query a range for a substring?
I'll give it a shot..thanks
"Gary''s Student" wrote: Sub kemikals() Dim s As String, v As String s = "SAU #1 -" n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n v = Cells(i, 1).Value If InStr(v, s) 0 Then MsgBox (Cells(i, 2).Value) End If Next End Sub -- Gary''s Student - gsnu200849 "chemicals" wrote: I can't seem to find a function to find cells with a particular portion of a string in them (much like a SQL query using LIKE). Is there such a function? If there is no such function then what would be the most efficient way to do this? I need to search a colum for several different values and then move 1 cell to the right and grab the value there. So fo example: SAU #1 - test 12 SAU #2 - development 25 SAU #3 - specs 12 So in the example I need the values of 12,25,12 but for the text in the first column the only known part that will be there is the first part (i.e. "SAU #1 -") Can Column.Find use a wildcard? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the best way to query a range for a substring?
And just so you know, VB has a Like operator that allows for wild card
patterns as well (check the help files for Like Operator to see what is available). Gary''s Student's macro can be rewritten this way to use it... Sub kemikals() n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n If Cells(i, 1).Value Like "SAU #1 -*" Then MsgBox (Cells(i, 2).Value) End If End Sub -- Rick (MVP - Excel) "chemicals" wrote in message ... I'll give it a shot..thanks "Gary''s Student" wrote: Sub kemikals() Dim s As String, v As String s = "SAU #1 -" n = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n v = Cells(i, 1).Value If InStr(v, s) 0 Then MsgBox (Cells(i, 2).Value) End If Next End Sub -- Gary''s Student - gsnu200849 "chemicals" wrote: I can't seem to find a function to find cells with a particular portion of a string in them (much like a SQL query using LIKE). Is there such a function? If there is no such function then what would be the most efficient way to do this? I need to search a colum for several different values and then move 1 cell to the right and grab the value there. So fo example: SAU #1 - test 12 SAU #2 - development 25 SAU #3 - specs 12 So in the example I need the values of 12,25,12 but for the text in the first column the only known part that will be there is the first part (i.e. "SAU #1 -") Can Column.Find use a wildcard? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Doing a countif against a substring of the range to be scanned | Excel Worksheet Functions | |||
Count the number of cells in a range with a string containing a specified substring | Excel Programming | |||
Count the number of cells in a range with a string containing a specified substring | Excel Programming | |||
Counting occurences of a substring in a range of cells. | Excel Worksheet Functions | |||
Searching for a substring in a range | Excel Programming |