Finding One String in Another String

[MT DOJ Help Desk]

I have the following in a macro.

InStr(1, cbLocate, "NIC/", vbTextCompare)

The problem is that sometimes the NIC/ tag appears twice in a record, and
the first occurance never follows a paragraph mark, and is always followed
by unusable data. However, the second tag is always preceded by a paragraph
mark, and is followed by good data. When the NIC/ tag appears only once in
the data, it is also always preceded by a paragraph mark and followed by
good data.

So what I need to do is make the above command find ONLY the NIC/ tag that
is preceded by a paragraph mark. Alternatively, I could make it pick up the
first occurance of the NIC/ tag when there is only one, and ignore it when
there are two in the record. Can anyone help me out with either of these
two possible solutions?

--Tom

 

[Jezebel]

possibility 1. Include the paragraph mark in what you're searching for:
instr(cbLocate, vbcr & "NIC/")

possibility 2: Use the Filter() function.

 

[MT DOJ Help Desk]

I thought about the your first suggestion, but I've been unable to get the
syntax right. With the syntax you have below, the command gets skipped
every time (it's embedded in an IF). I tried a couple of things but could
not get it to work.

I didn't previously know about the Filter() function, but after reading the
Help and thinking about it for a while, I don't see any way to make it work
for my purposes.

I have come up with a solution that works, but it's not as elegant as I
would like. Basically, in records where the NIC/ tag appears twice, the
first one is always preceded by a period. So I added a line to look for
".NIC/". Here's what I have now:

If InStr(1, cbLocate, ".NIC/", vbTextCompare) > 0 Then
StartPos = InStr(1, cbLocate, ".NIC/", vbTextCompare) + 4
StartPos = InStr(StartPos, cbLocate, "NIC/", vbTextCompare) + 4
cbLocate = Mid(cbLocate, StartPos, 10)
End If

This isn't a bad solution, but I'd like to eliminate one of the INSTR()
functions just because it would be a sleeker solution, and because I'd
really like to know how to make the paragraph marker part of the search
criteria in the INSTR() function.

FYI: Having the tag that I key on appear twice in some records is obviously
not ideal, but I have no control over that. The data comes from the FBI's
NCIC 2000 system and, well, they pretty much set the rules, so it's
something that I just have to live with.

--Tom

 

[Cindy M -WordMVP-]

Hi MT,

I thought about the your first suggestion, but I've been unable to get the
syntax right. With the syntax you have below, the command gets skipped
every time (it's embedded in an IF). I tried a couple of things but could
not get it to work.

Perhaps if you provide, exact, detailed information about what your data
really contains? WHAT is embedded in an IF?

Are you sure the "paragraph mark" in your data is a vbCR (Chr(13))? Where is
the string coming from?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Helmut Weber]

Hi Tom,
as Cindy said, first you have to make sure, that the
so called paragraph mark is vbcr at all. When processing
data that aren't genuine word data, there may sometimes
be a little surprise, and in word itself as well. E.g.
after inserting a vbcrlf by selection.typetext, it is
nevertheless not found by instr(). And besides that,
distinguishing between paragraphs and linefeeds doesn't
make much sense to me if the data is plain text.
Maybe a function like the following can help you,
as making your code a bit clearer, though some
refinement could be necessary:
Public Function LastPos(aStr$, bStr$) As Long
Dim aPos As Long
aPos = 0 ' redundant
While InStr(aPos + 1, aStr$, bStr$, vbTextCompare) > 0
aPos = InStr(aPos + 1, aStr$, bStr$, vbTextCompare)
Wend
LastPos = aPos
End Function
Sub test334()
MsgBox LastPos("abababab", "b") ' = 8
End Sub
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, W98

 

[MT DOJ Help Desk]

See below.

Hi MT,

Perhaps if you provide, exact, detailed information about what your data
really contains?

I'd love to, as that would make it much easier to explain what I need.
Unfortunately, it's confidential law enforcement data, so I can't post any
examples. In fact, I would most likely be fired for posting even a
sanitized version of the data.

WHAT is embedded in an IF?

Jezebel recommended the following:

instr(cbLocate, vbcr & "NIC/")

However, the command is embedded in an IF statement in my code, so using
Jezebel's suggestion, I would have:

If instr(cbLocate, vbcr & "NIC/") > 0 Then
<additional stuff>
End If

The problem is that this syntax ALWAYS causes the IF statement to evaluate
to false. So either it's not a vbCR, or there is something wrong the
command itself. I don't know enough about VBA to know if there is something
wrong with the command, and I'm not 100% certain that the character is a
vbCR, so at the moment I'm kind of shooting in the dark on this one.

Are you sure the "paragraph mark" in your data is a vbCR (Chr(13))?

I've wondered about this point, but haven't come up with a way to test what
the character really is. That's something that I'm going to try working on
tonight. However, my knowledge of VBA is limited, so I'm not sure how I'm
going to test the value. I do have code that reads the contents of the
clipboard and feeds it into a variable. Maybe I could copy the character,
run the code that feeds the clipboard contents into a variable, and then
test the variable to see what the character really is.

Where is the string coming from?

The data comes from a text-based system. I copy certain messages into a
Word document that I use as a hot plate--a temporary holding place for the
data. At the end of the shift I go into the Word document and process the
data as needed, and I have a number of macros that help me process the
information. When I show the codes in Word, the "paragraph mark" looks like
a standard Word paragraph mark

--Tom

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Going back to your original post, you might be better of to use Word's Find
command to look for "^pNIC/"

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[MT DOJ Help Desk]

Helmut,

I'm not 100% certain that the "paragraph mark" is really a paragraph mark,
but I haven't yet had a chance to test it. The data comes from a text based
system, get's copied into a Word document that acts as a temporary holding
place, then get's processed at the end of the shift. If I show the codes in
Word, the character looks like a standard Word paragraph mark.

I'm not trying to distinguish between a paragraph mark and a line feed. I'm
trying to distinguish between a data tag that is preceded by a "paragraph
mark", and the same tag when it is NOT preceded by a "paragraph mark".
Sometimes both situations occur in the same record, and that's were the
problem happens.

--Tom

 

[PeterS]

See below. (Like that. )

However, the command is embedded in an IF statement in my
code, so using Jezebel's suggestion, I would have:

If instr(cbLocate, vbcr & "NIC/") > 0 Then
<additional stuff>
End If

[...] I don't know enough about VBA to know if there is
something wrong with the command, [..]

Rest assured, the code is 100% correct.

The data comes from a text-based system.

This could be your problem. Plain text files often have a
vbCrLf instead a vbCr at the end of a paragraph.
(Chr(13) & Chr(10))

So, try: If InStr(cbLocate, vbCrLf & "NIC/") > 0 Then ...

If still no go, try replacing vCrLf with one of the
following:
- Chr(10)
- Chr(11)
- Chr(12)
- Chr(14)
- Chr(182)

One of those values will (!) work. Otherwise I hand in my
notebook. <g>

See ya
Peter

 

[MT DOJ Help Desk]

Doug,

At the point where the problem occurs, the data has been copied from the
document and then read from the clipboard into a variable called cbLocate.
Would the Find command work in that situation?

To be honest I'm not sure why I did it that way. I wrote the bulk of the
code a few months ago and don't remember what my reasoning was at the time.
I'll have to think about whether or not doing it my way has any real
advantages to just keeping everything in the document and picking out what I
need.

--Tom

"Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS"

 

[MT DOJ Help Desk]

Peter,

Since you liked it so much the first time... See below.

See below. (Like that. )

However, the command is embedded in an IF statement in my
code, so using Jezebel's suggestion, I would have:

If instr(cbLocate, vbcr & "NIC/") > 0 Then
<additional stuff>
End If

[...] I don't know enough about VBA to know if there is
something wrong with the command, [..]

Rest assured, the code is 100% correct.

The data comes from a text-based system.

This could be your problem. Plain text files often have a
vbCrLf instead a vbCr at the end of a paragraph.
(Chr(13) & Chr(10))

I thought about this, but just couldn't find the information in Help to test
it out.

So, try: If InStr(cbLocate, vbCrLf & "NIC/") > 0 Then ...

If still no go, try replacing vCrLf with one of the
following:
- Chr(10)
- Chr(11)
- Chr(12)
- Chr(14)
- Chr(182)

One of those values will (!) work. Otherwise I hand in my
notebook. <g>

Thanks for the help. It's the end of my shift, so I'll have to try these on
Monday. Will let you know what I find.

--Tom

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Tom,

Show us the code that you are using to load the information into the
variable. Are you sure that it is actually in the variable? If it is not
in the variable, then of course the Instr()'s will return false. Also, just
what to you mean by a variable?

If I run the following code on a document that contains a couple of NIC/,
one of which is preceded by a carriage return,
the second MsgBox statement returns the position of that carriage return in
the string cbLocate.

Dim cbLocate As String
cbLocate = ActiveDocument.Range
MsgBox InStr(1, cbLocate, "NIC/")
MsgBox InStr(1, cbLocate, vbCr & "NIC/")

I suspect that your problem is more with what you are doing before the
InStr() function than with that function itself.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Tom,

Stick a MsgBox cbLocate command in you code and see what it returns.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Helmut Weber]

Hi Tom,
let me make this clearer:

When the NIC/ tag appears only once in the data, it is
always preceded by a paragraph mark and followed
by good data.

If the tag appears only once, then the first tag is
identical to the last tag. Search for the last tag!

tag appears twice in a record,
second tag is always preceded by a paragraph mark,
and is followed by good data.

If the tag appears twice, then the second tag is
identical to the last tag. Search for the last tag!
Seems to me, when searching for the last tag you
can forget about vbcr or vbcrlf or anything of
that kind. Or am I missing something?
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, W98

 

[Bruce Brown]

Tom -

Playing around with your code I found that it worked fine when there
was a .NIC/ in front of the "good" NIC/ (the one that starts with a
paragraph mark or line feed) but did not find the "good" NIC/ when it
appeared alone without the other one in front of it.

The culprit seemed to be the if statement that hinged entirely on the
existence of .NIC/ without providing an alternative.

This small addition seems to make it work whether there's a .NIC/ in
the same record or not.

StartPos = 1
If InStr(1, cbLocate, ".NIC/", vbTextCompare) > 0 Then _
StartPos = InStr(1, cbLocate, ".NIC/", vbTextCompare) + 4
StartPos = InStr(StartPos, cbLocate, "NIC/", vbTextCompare) + 4
cbLocate = Mid(cbLocate, StartPos, 10)
MsgBox cbLocate


Thanks for the education in the InStr function. - Bruce

 

[MT DOJ Help Desk]

Sorry, guess I didn't look at your code close enough. I had thought about
the same concept (looking for the last tag), but couldn't see how to do it.
I've worked with While...Wend before, but just didn't think of that. I'm
pretty sure that your solution will work, but I need to review my data sets
to make sure. Nonetheless, I'd like to learn to do it the other way too (by
having INSTR() find the preceding paragraph mark), just to increase my own
knowledge.

--Tom

 

[MT DOJ Help Desk]

It turned out to be Chr(10). Thanks for the help.

--Tom

See below. (Like that. )

However, the command is embedded in an IF statement in my
code, so using Jezebel's suggestion, I would have:

If instr(cbLocate, vbcr & "NIC/") > 0 Then
<additional stuff>
End If

[...] I don't know enough about VBA to know if there is
something wrong with the command, [..]

Rest assured, the code is 100% correct.

The data comes from a text-based system.

This could be your problem. Plain text files often have a
vbCrLf instead a vbCr at the end of a paragraph.
(Chr(13) & Chr(10))

So, try: If InStr(cbLocate, vbCrLf & "NIC/") > 0 Then ...

If still no go, try replacing vCrLf with one of the
following:
- Chr(10)
- Chr(11)
- Chr(12)
- Chr(14)
- Chr(182)

One of those values will (!) work. Otherwise I hand in my
notebook. <g>

See ya
Peter

 

[MT DOJ Help Desk]

Bruce,

You're right, that's an error in my code. I had intended to provide the 3rd
option, but forgot to add the code. Fortunately, my new code is still in
the development and testing phase, so no harm done.

I've figured out how to make the INSTR() function also find the preceding
"paragraph mark". What looks like a paragraph mark in Word is actually a
line feed, so the following code works:

If InStr(cbLocate, Chr(10) & "NIC/") > 0 Then
StartPos = InStr(cbLocate, Chr(10) & "NIC/") + 5
cbLocate = Mid(cbLocate, StartPos, 10)
End If

By including the line feed character in the search, the "tag" that I'm
looking for becomes unique in every record, so there's no more need to
concerned about the fact that the ".NIC/" string appears in may records.

--Tom

 

[MT DOJ Help Desk]

Doug,

See my response to your other message.

--Tom

"Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS"

 

[MT DOJ Help Desk]

Doug,

See below.

"Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS"

Hi Tom,

Show us the code that you are using to load the information into the
variable. Are you sure that it is actually in the variable? If it is not
in the variable, then of course the Instr()'s will return false. Also, just
what to you mean by a variable?

cbLocate is the name of a string varibable. I know that the data is being
put into cbLocate because I inserted MsgBox cbLocate into my code, and I can
see the data when the message box pops up on the screen. What appears in
the diaglog box is formatted exactly as in the document. So I knew that
there was some kind of paragraph mark or line break in there, but I just
didn't know for sure what it was, and I didn't know how to test it. With
the help of a few other people who have posted messages in this thread, I
was able to figure out that the character is a line feed, so the following
code works:

If InStr(cbLocate, Chr(10) & "NIC/") > 0 Then
StartPos = InStr(cbLocate, Chr(10) & "NIC/") + 5
cbLocate = Mid(cbLocate, StartPos, 10)
End If

If I run the following code on a document that contains a couple of NIC/,
one of which is preceded by a carriage return,
the second MsgBox statement returns the position of that carriage return in
the string cbLocate.

Dim cbLocate As String
cbLocate = ActiveDocument.Range
MsgBox InStr(1, cbLocate, "NIC/")
MsgBox InStr(1, cbLocate, vbCr & "NIC/")

As indicated above, it was just a problem with what I was looking for using
the INSTR() function.

--Tom