Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
Hi,
I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
Can you give us an example of some of the more complicated "patterns" you
are trying to locate the position of? Rick "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
See "Like" in help, maybe
Regards, Peter T "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
Hi Rick,
The string I'm reading in from a file will include a product description and may include length and width values following a certain pattern. Below is an example string. There may be a couple variations on a theme which I could account for with a limited number of search patterns. "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" When this string was in a cell and I did the FIND command I entered " * IN X * YD" into the search box and it found it just fine. I can't depend on every string having this information (some list MISC SIZES) and I have to go somewhere else to get the dimensions (long story). "Rick Rothstein (MVP - VB)" wrote: Can you give us an example of some of the more complicated "patterns" you are trying to locate the position of? Rick "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
Hi Peter,
LIKE worked with wildcard characters. I think I can reliably use this with a subsequent INSTR command to get the location of the pattern within the string. Thanks much. "Peter T" wrote: See "Like" in help, maybe Regards, Peter T "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
After looking at Peter T's response, it occurs to me that I may have been
over thinking your question. I thought you were (eventually) trying to isolate the measurement section of the text. If that was your goal, then this is what I would suggest you do... Text = "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" If Text Like " * IN X * YDS" Then Measurement = Trim(Mid(T, InStrRev(T, " ", InStr(T, " IN X ") - 1))) End If But in reading Peter T's message, I now think you only wanted the If-Then part of the code above. Note: There are 2 spaces in front of the IN, not one as you showed in your message. Rick "RLang" wrote in message ... Hi Rick, The string I'm reading in from a file will include a product description and may include length and width values following a certain pattern. Below is an example string. There may be a couple variations on a theme which I could account for with a limited number of search patterns. "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" When this string was in a cell and I did the FIND command I entered " * IN X * YD" into the search box and it found it just fine. I can't depend on every string having this information (some list MISC SIZES) and I have to go somewhere else to get the dimensions (long story). "Rick Rothstein (MVP - VB)" wrote: Can you give us an example of some of the more complicated "patterns" you are trying to locate the position of? Rick "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
But then in reading your response to Peter T, I guess I didn't over think
the question too much after all... the code I posted should do what you want. Rick "Rick Rothstein (MVP - VB)" wrote in message ... After looking at Peter T's response, it occurs to me that I may have been over thinking your question. I thought you were (eventually) trying to isolate the measurement section of the text. If that was your goal, then this is what I would suggest you do... Text = "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" If Text Like " * IN X * YDS" Then Measurement = Trim(Mid(T, InStrRev(T, " ", InStr(T, " IN X ") - 1))) End If But in reading Peter T's message, I now think you only wanted the If-Then part of the code above. Note: There are 2 spaces in front of the IN, not one as you showed in your message. Rick "RLang" wrote in message ... Hi Rick, The string I'm reading in from a file will include a product description and may include length and width values following a certain pattern. Below is an example string. There may be a couple variations on a theme which I could account for with a limited number of search patterns. "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" When this string was in a cell and I did the FIND command I entered " * IN X * YD" into the search box and it found it just fine. I can't depend on every string having this information (some list MISC SIZES) and I have to go somewhere else to get the dimensions (long story). "Rick Rothstein (MVP - VB)" wrote: Can you give us an example of some of the more complicated "patterns" you are trying to locate the position of? Rick "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
I could, nay should have added in my post - search this ng for excellent
examples of the use of "Like" by Rick <g Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... But then in reading your response to Peter T, I guess I didn't over think the question too much after all... the code I posted should do what you want. Rick "Rick Rothstein (MVP - VB)" wrote in message ... After looking at Peter T's response, it occurs to me that I may have been over thinking your question. I thought you were (eventually) trying to isolate the measurement section of the text. If that was your goal, then this is what I would suggest you do... Text = "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" If Text Like " * IN X * YDS" Then Measurement = Trim(Mid(T, InStrRev(T, " ", InStr(T, " IN X ") - 1))) End If But in reading Peter T's message, I now think you only wanted the If-Then part of the code above. Note: There are 2 spaces in front of the IN, not one as you showed in your message. Rick "RLang" wrote in message ... Hi Rick, The string I'm reading in from a file will include a product description and may include length and width values following a certain pattern. Below is an example string. There may be a couple variations on a theme which I could account for with a limited number of search patterns. "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" When this string was in a cell and I did the FIND command I entered " * IN X * YD" into the search box and it found it just fine. I can't depend on every string having this information (some list MISC SIZES) and I have to go somewhere else to get the dimensions (long story). "Rick Rothstein (MVP - VB)" wrote: Can you give us an example of some of the more complicated "patterns" you are trying to locate the position of? Rick "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
Thank you for that very nice compliment... I really appreciate it. As for
searching posts by me that use the Like operator, the compiled VB newsgroups probably have a ton of them as I have been an advocate of the Like operator for years over there. Rick "Peter T" <peter_t@discussions wrote in message ... I could, nay should have added in my post - search this ng for excellent examples of the use of "Like" by Rick <g Regards, Peter T "Rick Rothstein (MVP - VB)" wrote in message ... But then in reading your response to Peter T, I guess I didn't over think the question too much after all... the code I posted should do what you want. Rick "Rick Rothstein (MVP - VB)" wrote in message ... After looking at Peter T's response, it occurs to me that I may have been over thinking your question. I thought you were (eventually) trying to isolate the measurement section of the text. If that was your goal, then this is what I would suggest you do... Text = "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" If Text Like " * IN X * YDS" Then Measurement = Trim(Mid(T, InStrRev(T, " ", InStr(T, " IN X ") - 1))) End If But in reading Peter T's message, I now think you only wanted the If-Then part of the code above. Note: There are 2 spaces in front of the IN, not one as you showed in your message. Rick "RLang" wrote in message ... Hi Rick, The string I'm reading in from a file will include a product description and may include length and width values following a certain pattern. Below is an example string. There may be a couple variations on a theme which I could account for with a limited number of search patterns. "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" When this string was in a cell and I did the FIND command I entered " * IN X * YD" into the search box and it found it just fine. I can't depend on every string having this information (some list MISC SIZES) and I have to go somewhere else to get the dimensions (long story). "Rick Rothstein (MVP - VB)" wrote: Can you give us an example of some of the more complicated "patterns" you are trying to locate the position of? Rick "RLang" wrote in message ... Hi, I've used the FIND command in conjunction with wildcard characters to find string patterns in a worksheet cell. Can anyone tell me if I can do the same thing in VBA against a string variable that I've read in from a file rather than referencing a cell? Apparently INSTR doesn't allow wildcard characters. Thanks much. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Command
Text = "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS"
If Text Like " * IN X * YDS" Then Measurement = Trim(Mid(T, InStrRev(T, " ", InStr(T, " IN X ") - 1))) End If Note: There are 2 spaces in front of the IN, not one as you showed in your message. Of course, IF there are ALWAYS two or more spaces if front of the measurement, there is a much simpler assignment statement available... Text = "IJ180C-10 WHITE C/T W/COMPLY 54 IN X 50 YDS" If Text Like " * IN X * YDS" Then Measurement = Mid$(T, InStrRev(T, " ") + 2) End If Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Command | Excel Discussion (Misc queries) | |||
VBA - Find command | Excel Programming | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Find Command | Excel Programming | |||
Find Command | Excel Programming |