Instr Vs instrRev

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
Hello, whats the differance between these two.
I would like to know how many charactors my string is back from the very end of the other string, but it keeps giving me from the front!

Thanks![/img]
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

CyberGuy2004

Board Regular
Joined
May 9, 2007
Messages
65
Hi, Kavy.

Looks to me like both functions return the index of the "search" string from the beginning of the "within" string. The difference is that InstrRev starts looking at the end of the "within" string. So, Instr("This String has back in it near the front and near the back as well","back") gives 17, whilst InstrRev("This String has back in it near the front and near the back as well","back") gives 56.

I suppose you could subtract the result of the InstrRev function from the length of the "within" string and add 1 to get the length from the end of the string.

eta: (I didn't find the VBA Help for InstrRev helpful at all, did you? No example, no "see also" link. It must have been one of the last things added.)

Any help at all?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi Kavy

InStr() gives you the position of the FIRST match. InStrRev gives you the position of the LAST match. Both positions from the start position of the string.

InStr("between","e") is equal to 2, the position of the first "e".

InStrRev ("between","e") is equal to 6, the position of the last "e".

If you want to know how many characters the string is back from the very end of the other string, take the difference from the legth of the string being searched.

Ex:

Len("between")-InStr("between","een") +1

is equal to 3, the start of the first substring "een" counting from the end of "between".

Hope this helps
PGC
 

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
Thanks guys, and ya i found the vb help confusing, and i don't know why i couldn't figure this one out on my own using trial and error could wrap my head around it. I actually used some guys code i found on the net where he changes the function of instrev but u guys way is much simpler and better!

Only thing is i am using file names with more then one "\" so i have to use instrRev
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,087
Messages
5,857,285
Members
431,868
Latest member
servox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top