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]
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,881
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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