I know a few ways to search or find terms in a string, but what method
is the best/quickest/easiest?

For example, if I have a string and want to find a different string
within that.

I want to see if the string:

"TEMP(302)WPRDRAMT|input_range = "0" "999999.99" ; |MaxInput = 10|"

Contains the string:

"input_range"

And if so I want to extract the terms "0" and "999999.99".

The way I've done this is the past (being new to VBA) is to loop
through the string and pull out substrings in blocks the same length
as the string I'm searching for (using MID) until I get a match, but
is there a SEARCH/FIND etc command in VBA?

I'd then use the " as delimiters to get the two numeric values I'm
after, but again is there a better way than using MID and stepping
through the string character by character?

Thanks.

(I've not explained that very well so please question me if you need
clarification)

by my opinion, best way of working is

1- use the instr function to see if the string contains the search string
2- use the mid function to copy the data you need


--
Best regards
Luc Nuyts
www.scriptingIT.be


"(E-Mail Removed)" wrote:

> I know a few ways to search or find terms in a string, but what method
> is the best/quickest/easiest?
>
> For example, if I have a string and want to find a different string
> within that.
>
> I want to see if the string:
>
> "TEMP(302)WPRDRAMT|input_range = "0" "999999.99" ; |MaxInput = 10|"
>
> Contains the string:
>
> "input_range"
>
> And if so I want to extract the terms "0" and "999999.99".
>
> The way I've done this is the past (being new to VBA) is to loop
> through the string and pull out substrings in blocks the same length
> as the string I'm searching for (using MID) until I get a match, but
> is there a SEARCH/FIND etc command in VBA?
>
> I'd then use the " as delimiters to get the two numeric values I'm
> after, but again is there a better way than using MID and stepping
> through the string character by character?
>
> Thanks.
>
> (I've not explained that very well so please question me if you need
> clarification)
>
>

>I know a few ways to search or find terms in a string, but what method
> is the best/quickest/easiest?
>
> For example, if I have a string and want to find a different string
> within that.
>
> I want to see if the string:
>
> "TEMP(302)WPRDRAMT|input_range = "0" "999999.99" ; |MaxInput = 10|"
>
> Contains the string:
>
> "input_range"
>
> And if so I want to extract the terms "0" and "999999.99".

Part of the decision rests on the EXACT format of your Source string... if
input_range is in the Source string, is it always followed by space/equal
sign/space, are there always exactly two values following it, are the values
always surrounded by quote marks, do you want the quote marks in your
result, etc., etc.? Here is an approach given that the Source String we see
is what you actually have...

Dim Source As String
Dim LowerRange As String
Dim UpperRange As String
Dim Fields() As String
Source = """TEMP(302)WPRDRAMT|input_range = ""0"" ""999999.99"" ; |MaxInput
= 10|"""
If InStr(1, Source, "input_range", vbTextCompare) Then
Fields = Split(Trim$(Split(Source, "input_range")(1)))
LowerRange = Fields(1)
UpperRange = Fields(2)
End If

Note that the "extra" quote marks in the Source assignment statement are
required in a String constant assignment (used here for example purposes) in
order to keep the quote marks in the String where you showed them in your
posting. They would not be required if the Source string came in via a
TextBox or was read in from a file.

Rick

You have to consider if the CASE makes a difference in you text. You may
want to force all text to uppercase before you do a comparison. Instr is
case sensitive, while using "=" is not case sensitive.

"Rick Rothstein (MVP - VB)" wrote:

> >I know a few ways to search or find terms in a string, but what method
> > is the best/quickest/easiest?
> >
> > For example, if I have a string and want to find a different string
> > within that.
> >
> > I want to see if the string:
> >
> > "TEMP(302)WPRDRAMT|input_range = "0" "999999.99" ; |MaxInput = 10|"
> >
> > Contains the string:
> >
> > "input_range"
> >
> > And if so I want to extract the terms "0" and "999999.99".
>
> Part of the decision rests on the EXACT format of your Source string... if
> input_range is in the Source string, is it always followed by space/equal
> sign/space, are there always exactly two values following it, are the values
> always surrounded by quote marks, do you want the quote marks in your
> result, etc., etc.? Here is an approach given that the Source String we see
> is what you actually have...
>
> Dim Source As String
> Dim LowerRange As String
> Dim UpperRange As String
> Dim Fields() As String
> Source = """TEMP(302)WPRDRAMT|input_range = ""0"" ""999999.99"" ; |MaxInput
> = 10|"""
> If InStr(1, Source, "input_range", vbTextCompare) Then
> Fields = Split(Trim$(Split(Source, "input_range")(1)))
> LowerRange = Fields(1)
> UpperRange = Fields(2)
> End If
>
> Note that the "extra" quote marks in the Source assignment statement are
> required in a String constant assignment (used here for example purposes) in
> order to keep the quote marks in the String where you showed them in your
> posting. They would not be required if the Source string came in via a
> TextBox or was read in from a file.
>
> Rick
>
>

Hi Joel,

'-----------------
You have to consider if the CASE makes a difference in you text. You may
want to force all text to uppercase before you do a comparison. Instr is
case sensitive, while using "=" is not case sensitive.
'-----------------

Try:

'=============>>
Public Sub Tester()
Dim iPos As Long
Dim jPos As Long
Const sStr As String = "A CAT AND A DOG"
Const sStr2 As String = "cat"

iPos = InStr(1, sStr, sStr2, vbTextCompare)
jPos = InStr(1, sStr, sStr2, vbBinaryCompare)

MsgBox Prompt:="vbTextCompare: " & iPos _
& vbNewLine _
& "vbBinaryCompare : " & jPos
End Sub
'<<=============


---
Regards,
Norman

>> If InStr(1, Source, "input_range", vbTextCompare) Then
>
> You have to consider if the CASE makes a difference in you text. You may
> want to force all text to uppercase before you do a comparison. Instr is
> case sensitive, while using "=" is not case sensitive.

InStr has optional arguments... if you specify the optional starting point
for your search in the 1st argument, then an optional 4th argument is
available to take care of casing issues. In my statement above,
vbTextCompare forces a case insensitive search to take place (you can
specify vbBinaryCompare which forces an case sensitive search to take place,
but specifying it is unnecessary as it is the default condition for searches
when not specified).

Rick

Hi Rick,

'---------------
[...]
In my statement above,
vbTextCompare forces a case insensitive search to take place (you can
specify vbBinaryCompare which forces an case sensitive search to take place,
but specifying it is unnecessary as it is the default condition for searches
when not specified).
'---------------

Perhaps you would permit one small addendum?

I believe that, if the optional Comparison argument of the
Instr method is omitted, the text comparison method is
determined by the value of the Option Compare statement;
in the absence of an Option Compare declaaration, the
default text comparison method would be Binary,


---
Regards,
Norman

You also were incorrect about the Equal sign.
Using = IS case sensative.

? "A" = "a"
False
? "a" = "a"
True
? "A" = "A"
True

--
Regards,
Tom Ogilvy


"Joel" wrote:

> You have to consider if the CASE makes a difference in you text. You may
> want to force all text to uppercase before you do a comparison. Instr is
> case sensitive, while using "=" is not case sensitive.
>
> "Rick Rothstein (MVP - VB)" wrote:
>
> > >I know a few ways to search or find terms in a string, but what method
> > > is the best/quickest/easiest?
> > >
> > > For example, if I have a string and want to find a different string
> > > within that.
> > >
> > > I want to see if the string:
> > >
> > > "TEMP(302)WPRDRAMT|input_range = "0" "999999.99" ; |MaxInput = 10|"
> > >
> > > Contains the string:
> > >
> > > "input_range"
> > >
> > > And if so I want to extract the terms "0" and "999999.99".
> >
> > Part of the decision rests on the EXACT format of your Source string... if
> > input_range is in the Source string, is it always followed by space/equal
> > sign/space, are there always exactly two values following it, are the values
> > always surrounded by quote marks, do you want the quote marks in your
> > result, etc., etc.? Here is an approach given that the Source String we see
> > is what you actually have...
> >
> > Dim Source As String
> > Dim LowerRange As String
> > Dim UpperRange As String
> > Dim Fields() As String
> > Source = """TEMP(302)WPRDRAMT|input_range = ""0"" ""999999.99"" ; |MaxInput
> > = 10|"""
> > If InStr(1, Source, "input_range", vbTextCompare) Then
> > Fields = Split(Trim$(Split(Source, "input_range")(1)))
> > LowerRange = Fields(1)
> > UpperRange = Fields(2)
> > End If
> >
> > Note that the "extra" quote marks in the Source assignment statement are
> > required in a String constant assignment (used here for example purposes) in
> > order to keep the quote marks in the String where you showed them in your
> > posting. They would not be required if the Source string came in via a
> > TextBox or was read in from a file.
> >
> > Rick
> >
> >

> In my statement above,
> vbTextCompare forces a case insensitive search to take place (you can
> specify vbBinaryCompare which forces an case sensitive search to take
> place,
> but specifying it is unnecessary as it is the default condition for
> searches
> when not specified).
> '---------------
>
> Perhaps you would permit one small addendum?
>
> I believe that, if the optional Comparison argument of the
> Instr method is omitted, the text comparison method is
> determined by the value of the Option Compare statement;
> in the absence of an Option Compare declaaration, the
> default text comparison method would be Binary,

That may be true... but I don't know for sure as the only Option statement I
ever use is Option Explicit. You must understand, I come from the compiled
VB world and, in a work environment, those other Option statements just seem
to lead to confusion when code is being developed and/or maintained by
multiple people.

Rick

Hi Rick,

'----------------
> I believe that, if the optional Comparison argument of the
> Instr method is omitted, the text comparison method is
> determined by the value of the Option Compare statement;
> in the absence of an Option Compare declaaration, the
> default text comparison method would be Binary,

That may be true... but I don't know for sure as the only Option statement I
ever use is Option Explicit. You must understand, I come from the compiled
VB world and, in a work environment, those other Option statements just seem
to lead to confusion when code is being developed and/or maintained by
multiple people.
'----------------

In the case of the Instr method, the message should be:
always explicitly state the text comparison method; then.
any confusion will be avoided.

In fact, why limit this dictum to Instr?


---
Regards,
Norman