Instr Function Explanation Anyone ???
Author Message
Instr Function Explanation Anyone ???

I went to Microsoft to find some info about the function Instr.  I need to
perform a search with a string similar to their example I found below.  Can
anyone explain to me Microsoft's example??  I am little confused by the
parameters used and the explanation it gives back to me??

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP"   ' String to search in.
SearchChar = "P"   ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1)   ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0)   ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar)   ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W")   ' A binary comparison starting at

My problem is this:

I need to scan within SearchString for blanks/spaces characters.  When I
find one, then place the values to the left and right of it in seperate
columns.  For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
column.

I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?

my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' '  (note I am searching for a
space/blank character)

So would then my code be like:

Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)

How do I get whatever is returned from the Instr function to a column in a
table??

Any help would be great.

Rey

Sat, 05 Jul 2003 07:29:37 GMT
Instr Function Explanation Anyone ???
Instr() simply tells you where it found what you told it to search for.  The various arguments let
you pick the starting point of the search and and whether or not a match is case sensitive or not.

If all you need to do is break a string or 3 "words" that are separated by a single space, then
Split() is what you need.  For the sake of example, I'm loosely defing a "word" as a consecutive
series of non-space characters...

myString = 'John Smith A'
arParts = Split(myString," ")

What you get is a 3 element array (i.ie, arPart has a UBound() of 2)

arParts(0) ==> John
arParts(1) ==> Smith
arParts(2) ==> A

Of course, if there aren't always 3 "words" in myString or if they are separated by more than one
space, then the above will break.
For example, neither
"John Smith"
nor
"John    Smith      A"
will be parsed correctly using a simple Split().

The right method to use to parse a string into pieces depends on what you know to always be true
about what the string might legitimately contain.

--
Michael Harris
Microsoft.MVP.Scripting
--

Please do not email questions - post them to the newsgroup instead.
--

Quote:

> I went to Microsoft to find some info about the function Instr.  I need to
> perform a search with a string similar to their example I found below.  Can
> anyone explain to me Microsoft's example??  I am little confused by the
> parameters used and the explanation it gives back to me??

> Dim SearchString, SearchChar, MyPos
> SearchString ="XXpXXpXXPXXP"   ' String to search in.
> SearchChar = "P"   ' Search for "P".
> MyPos = Instr(4, SearchString, SearchChar, 1)   ' A textual comparison
> starting at position 4. Returns 6.
> MyPos = Instr(1, SearchString, SearchChar, 0)   ' A binary comparison
> starting at position 1. Returns 9.
> MyPos = Instr(SearchString, SearchChar)   ' Comparison is binary by default
> (last argument is omitted). Returns 9.
> MyPos = Instr(1, SearchString, "W")   ' A binary comparison starting at

> My problem is this:

> I need to scan within SearchString for blanks/spaces characters.  When I
> find one, then place the values to the left and right of it in seperate
> columns.  For example, I would need to scan 'John Smith A' and then place
> 'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
> column.

> I think this is how my code would read, but I am confused on how to place
> the results into my table to the correct columns?

> my search string would be SearchString = 'John Smith A'
> my SearchChar would be SearchChar = ' '  (note I am searching for a
> space/blank character)

> So would then my code be like:

> Dim SearchString, SearchChar, MyPos
> SearchString = 'John Smith A'
> SearchChar = ' '
> MyPos = Instr(1, SearchString, SearchChar, 0)

> How do I get whatever is returned from the Instr function to a column in a
> table??

> Any help would be great.

> Rey

Sat, 05 Jul 2003 09:40:20 GMT
Instr Function Explanation Anyone ???

Quote:
> Instr() simply tells you where it found what you told it to search for.

The various arguments let
Quote:
> you pick the starting point of the search and and whether or not a match

is case sensitive or not.
Quote:

> If all you need to do is break a string or 3 "words" that are separated by

a single space, then
Quote:
> Split() is what you need.  For the sake of example, I'm loosely defing a

"word" as a consecutive
Quote:
> series of non-space characters...

> myString = 'John Smith A'
> arParts = Split(myString," ")

> What you get is a 3 element array (i.ie, arPart has a UBound() of 2)

> arParts(0) ==> John
> arParts(1) ==> Smith
> arParts(2) ==> A

> Of course, if there aren't always 3 "words" in myString or if they are

separated by more than one

Quote:
> space, then the above will break.
> For example, neither
> "John Smith"
> nor
> "John    Smith      A"
> will be parsed correctly using a simple Split().

Some will suggest using regular expressions here, but in the case where
there might be multiple whitespace characters, I sometimes do this:

const dblspace = "  " ' that's two blank characters!
myString = "John    Smith    A"
editString = replace( myString, vbtab, " " )
do while instr( editString, dblspace )
editString = replace( editString, dblspace, " " )
loop
editString = trim( editString )

Quote:
> The right method to use to parse a string into pieces depends on what you

know to always be true

Quote:
> about what the string might legitimately contain.

Nicely said! Applies to lots of different situations that crop up.

/Al

Quote:
> --
> Michael Harris
> Microsoft.MVP.Scripting
> --

> Please do not email questions - post them to the newsgroup instead.
> --

Quote:
> > I went to Microsoft to find some info about the function Instr.  I need
to
> > perform a search with a string similar to their example I found below.
Can
> > anyone explain to me Microsoft's example??  I am little confused by the
> > parameters used and the explanation it gives back to me??

> > Dim SearchString, SearchChar, MyPos
> > SearchString ="XXpXXpXXPXXP"   ' String to search in.
> > SearchChar = "P"   ' Search for "P".
> > MyPos = Instr(4, SearchString, SearchChar, 1)   ' A textual comparison
> > starting at position 4. Returns 6.
> > MyPos = Instr(1, SearchString, SearchChar, 0)   ' A binary comparison
> > starting at position 1. Returns 9.
> > MyPos = Instr(SearchString, SearchChar)   ' Comparison is binary by
default
> > (last argument is omitted). Returns 9.
> > MyPos = Instr(1, SearchString, "W")   ' A binary comparison starting at

> > My problem is this:

> > I need to scan within SearchString for blanks/spaces characters.  When I
> > find one, then place the values to the left and right of it in seperate
> > columns.  For example, I would need to scan 'John Smith A' and then
place
> > 'John' in FirstName column, 'Smith' in LastName column, and 'A' in
MidName
> > column.

> > I think this is how my code would read, but I am confused on how to
place
> > the results into my table to the correct columns?

> > my search string would be SearchString = 'John Smith A'
> > my SearchChar would be SearchChar = ' '  (note I am searching for a
> > space/blank character)

> > So would then my code be like:

> > Dim SearchString, SearchChar, MyPos
> > SearchString = 'John Smith A'
> > SearchChar = ' '
> > MyPos = Instr(1, SearchString, SearchChar, 0)

> > How do I get whatever is returned from the Instr function to a column in
a
> > table??

> > Any help would be great.

> > Rey

Sat, 05 Jul 2003 11:51:33 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages