Why doesn't INSTREV work for me?

D

dsimcox

I'm using INSTREV to retrieve the location of a character in a string by
searching from the end of the string.

When I type the following in the immediate window:

?InStrRev("1234567-89", "-")

I get an answer of 8.

I was expecting 3. (The position of the "-" is 3 characters from the
right-hand side of the string)

Am I interpreting this incorrectly?
 
R

Ron Rosenfeld

I'm using INSTREV to retrieve the location of a character in a string by
searching from the end of the string.

When I type the following in the immediate window:

?InStrRev("1234567-89", "-")

I get an answer of 8.

I was expecting 3. (The position of the "-" is 3 characters from the
right-hand side of the string)

Am I interpreting this incorrectly?
Click to expand...

Yes.

Although it starts looking from the end of the string, it still gives the
position numbered from the beginning of the string.

?InStrRev("12-34567-89", "-")

would give an answer of 9.
--ron
 
R

Rick Rothstein \(MVP - VB\)

That is the way the InStrRev function works... it simply performs its search
for your specified character from the right to left, but it tells you which
character position in the text, counting from the left, the search character
is located at... it is **not** a Right function replacement of any kind. I'm
guessing from your question that you are trying to isolate the characters
after the last dash. Just use the Mid function omitting the optional 3rd
argument...

Text = "1234567-89"
LastDashLocation = InStrRev(Text, "-")
? Mid$(Text, LastDashLocation + 1)

or, in one step...

? Mid$("1234567-89", InStrRev(Text, "-") + 1)

Rick
 
M

Mike H

Hi,

It is working for you, it returns the position of the last occurence of the
search character not the position from the right hand side

Try this

myposition = Len("1234567-89") - (InStrRev("1234567-89", "-", -1,
vbBinaryCompare) - 1)

Mike
 
D

dsimcox

Thank you, Rick and Mike.

That possiblity never occurred to me. I understand how it works now - but I
think I would have designed it to work the other way!

Appreciate you help this morning.
 
R

Rick Rothstein \(MVP - VB\)

Thank you, Rick and Mike.

Do you have something against Ron? said:
That possiblity never occurred to me. I understand how it works
now - but I think I would have designed it to work the other way!
Click to expand...

That would make working with it rather difficult I would think, especially
in situations where both the InStr and InStrRev functions were used in the
same expression. My guess is that you are thinking that because of the
immediate use you need to put it to and are not looking at the "bigger
picture" of the situations where it can be used at.

Rick
 
M

Mike H

Rick,
Do you have something against Ron?<g> (He also answered you.)
Click to expand...

Clearly we have a posting problem because the only answers I see are from
you and I, nothing from Ron?

Mike
 
R

Rick Rothstein \(MVP - VB\)

Do you have something against Ron? said:
Clearly we have a posting problem because the only answers I see are
from you and I, nothing from Ron?
Click to expand...

My newsreader shows Ron Rosenfeld as the first responder to this thread (his
message was posted approximately 4 minutes before mine was).

Rick
 
M

Mike H

Nope,

In order
OP
You
Me.Same time as yourself
OP +18 Minutes
Me
You
Me
You
and in a few minures this one.

Mike
Mike
 
R

Rick Rothstein \(MVP - VB\)

Nope,
In order
OP
You
Me.Same time as yourself
Click to expand...

I show your post as coming in 4 minutes after mine.
OP +18 Minutes
Click to expand...

However, I do see the 18 gap for the OP though.
Me
You
Me
You
and in a few minures this one.
Click to expand...

With the exception of Ron's posts, I see the same message tree as you do.
Strange.

By the way, I see from your parallel post in this sub-thread that you are
using a web newsreader... I am using Window (Vista) Mail via a direct
account set up with the microsoft.public.excel.misc news server.

Rick
 
R

Ron Rosenfeld

Ron, if you haven't already done so, you should read the sub-thread started
by Mike H... it seems that you posting is not visible in Microsoft's online
newsreader....

http://www.microsoft.com/office/com...?dg=microsoft.public.excel.misc&lang=en&cr=US

I'm not sure why, or if this is an ongoing problem with your postings or
not, but you might want to try and discover why.

Rick
Click to expand...

Thanks for the tip, Rick.

This is not the first time that I have felt ignored. I will check out that
link.
--ron
 
R

Ron Rosenfeld

Ron, if you haven't already done so, you should read the sub-thread started
by Mike H... it seems that you posting is not visible in Microsoft's online
newsreader....

http://www.microsoft.com/office/com...?dg=microsoft.public.excel.misc&lang=en&cr=US

I'm not sure why, or if this is an ongoing problem with your postings or
not, but you might want to try and discover why.

Rick
Click to expand...

Nothing is apparent, although obviously you were able to see my post using your
newsreader and not going through the web version.

I have sent a tech support email to Forte (I use Agent as my newsreader) and
I'll see what they say. Whatever it is, it seems to be only an intermittent
problem. I notice that although my first response to the OP did not show up,
others of my responses did. In addition, your post to me shows up on the MS
newsgroup as a blank post from an unknown person!
--ron
 
R

Rick Rothstein \(MVP - VB\)

I just looked back on the link you posted earlier... Ron is still not in the
original thread, but his latest post shows up in the new thread... strange.

By the way, this is the first time I have really looked a web newsreader...
what a horrible interface it provides... the same thread is broken up into
two (so far) separate threads even though the subject is the same and the
newer thread is really just a continuation of the older one. How does anyone
follow a continuous thread of posts if the (web) newsreader is breaking them
into separate threads with on apparent (at least to me) rhyme or reason?

Rick
 
M

Mike H

Curiouser & Curiouser the starter of this thread - presunably Rick - is
showing up as 'Unknown' to me. Clearly we have a gremlin of; as yet,
indeterminate origin

Mike
 
R

Rick Rothstein \(MVP - VB\)

Ron, if you haven't already done so, you should read the sub-thread
Nothing is apparent, although obviously you were able to see my post using
your
newsreader and not going through the web version.

I have sent a tech support email to Forte (I use Agent as my newsreader)
and
I'll see what they say. Whatever it is, it seems to be only an
intermittent
problem. I notice that although my first response to the OP did not show
up,
others of my responses did. In addition, your post to me shows up on the
MS
newsgroup as a blank post from an unknown person!
Click to expand...

Yes, I just saw that and I posted a message back to Mike commenting what a
horrible interface the web newsreader provides. Not only is the new thread
shown as started by an unknown person (what is with that?), the thread is
totally separated from the original thread of which it is a part... my
question was, how does anyone follow a threaded set of postings if the (web)
newsreader is breaking them apart with no apparent rhyme or reason?

Rick
 
R

Ron Rosenfeld

Yes, I just saw that and I posted a message back to Mike commenting what a
horrible interface the web newsreader provides. Not only is the new thread
shown as started by an unknown person (what is with that?), the thread is
totally separated from the original thread of which it is a part... my
question was, how does anyone follow a threaded set of postings if the (web)
newsreader is breaking them apart with no apparent rhyme or reason?

Rick
Click to expand...

Poorly?
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Instrrev function 4
Why not accept wildcards for REPLACE as well as FIND ? 5
find a ?, text acting as number, mysterious single quote 5
find and replace numeric strings in larger text strings 8
Problem Generating Key = reprise 4
Using "InstrRev" 2
Linkformat problem 8
Excel 2003 VBA string literal help location 9

Top