Best/Easiest way to search/find in a string

[skulkrinbait]

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)

 

[Luc]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[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.

 

[Norman Jones]

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
'<<=============

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Norman Jones]

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,

 

[Tom Ogilvy]

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Norman Jones]

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?

 

[Rick Rothstein \(MVP - VB\)]

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?

The "text comparison method" may not always be the appropriate one. For
example (and this is a far-fetched example), what if you were looking for
USB (as in USB computer port) in a large piece of text... a case insensitive
search might turn locate the "usb" in "husband" before it finds the USB you
are looking for. Being able to specify a case sensitive search would (well,
at least should) eliminate that possibility. An argument could be made for
making the case insensitive option the default one, but providing the
capability for both is desirable (in my opinion). However, Microsoft has
already fixed the default for InStr (as well as other functions) ages ago,
so there is no point in considering the "what ifs" for them. More important
than which case should be the default, I wish an option for "whole word"
would have been implemented for InStr so that "the" wouldn't be found in
"other" before "the" was found standing alone. But that is another wish that
will go unanswered I'm afraid.

Rick

 

[Tom Ogilvy]

Norman meant that the dictum is to always explicitly state the method of
comparison within the instr function command rather than depend on any
default/global setting. He used the term text comparision to mean how the
comparison is to be performed (binary or text) rather than that the
vbTextCompare be used as you appeared to interpret his suggestion. Futher
he envisioned this as a guiding principle in general across all functions
where appropriate - an Axiom

 

[Norman Jones]

Hi Tom,

An admirably clear exposition of my intent!

Thank you.

 

[Rick Rothstein \(MVP - VB\)]

Norman meant that the dictum is to always explicitly state the method of

An admirably clear exposition of my intent!

Whoops!!! Sorry, I completely missed what you were saying. Yes, explicitly
specifying values instead of relying on defaults would be a good practice to
get into... and for object properties I always do that; but I am a long time
BASIC and VB user (started programming in BASIC back in 1981), so some "bad"
habits have become totally ingrained over the years. Relying on the default
start position of 1 and the default text comparison method of "case
sensitive" for the InStr function is one of those bad habits... after nearly
30 years of not specifying them, I probably will never remember to do so in
the "heat" of a programming session.

Rick