find in reverse

[smw]

I need to find the last occurrence of a character in a string.
Does anyone know of a way to make find() search from the end of a string,
rather than from the beginning.

Thank you
Steven Wheeler

 

[T. Valko]

Try this...

Seems like it should be easier than this.

x234x678x0

Find the position of the last instance of "x":

=LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))

 

[T. Valko]

Ok, I knew it had to be "easier".

x234x678x0

Find the position of the last instance of "x":

=SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))))

Note that SUBSTITUTE is case sensitive. So X and x don't match!

 

[Rick Rothstein]

You can also do it with this array-entered** formula...

=MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15))

**commit formula using Ctrl+Shift+Enter, not just Enter by itself.

Two side benefits of the above formula is that it returns 0 as the result if
"x" is not in A1 (your formula returns a #VALUE! error) and it is case
insensitive (it finds the last "x" or "X").

 

[Rick Rothstein]

Sorry, I meant to use 99, not 15 for the ROW array (still array-entered)...

=MAX((MID(A1,ROW(1:99),1)="x")*ROW(1:99))

I used 15 originally for debugging purposes and then forgot to reset it
afterwards. For the OP... the formula (with the 99s) assumes your text in A1
will not be longer than 99 characters. If it could be, just change both 99s
to a (same) value equal to or larger than the maximum number of characters
that can appear in A1.

 

[Jacob Skaria]

Hi

I understand you are looking to find from the end of a string and not a
range..

If you are looking to implement this in code then use the function InstrRev
which will return the position of the occurence from end.
OR
If you are looking to use it as a function from worksheet you can copy the
below to VBE and use this function from Insert|Function|UserDefined

If this post helps click Yes
---------------
Jacob Skaria

Function FindStringfromRev(strString, strFindString)
FindStringfromRev = InStrRev(strString, strFindString)
End Function

To copy this 'Launch VBE using short-key Alt+F11. On the left treeview right
click 'This Workbook '. Insert a module and paste the below code. Save. Get
back to Workbook. Set the Security level to low/medium in
(Tools|Macro|Security).

 

[T. Valko]

I see I'm not having any "influence" on your use of expressions like:

ROW(1:99)

<g>

 

[Rick Rothstein]

I see I'm not having any "influence" on your use of expressions like:

ROW(1:99)

<g>

It's nearly 3:00 AM here and I'm about to go to sleep... your reference
escapes me at the moment... would you be so kind as to refresh my memory
about you meant by that?

 

[jaf]

Hi Rick,
Can this be done in VBA?
For instance finding the last "\" in a path.

I hacked this out. There has to be a more elegant way.

Sub myTest()
myPath = "c:\users\jaf\text.txt"
n = 0
For i = 1 To Len(myPath)
ctr1 = Mid(myPath, i, 1)
last = InStr(1, ctr1, "\", vbTextCompare)

If last >= 1 Then
n = n + 1
ctr2 = i
End If

Next

myFilename = Mid(myPath, ctr2 + 1, Len(myPath) - ctr2)
Debug.Print InStr(ctr2, myPath, "\"), myFilename

End Sub


John

 

[Rick Rothstein]

You can use the InStrRev function to search backwards through text. To get
the filename from a path...

FileName = Mid(Path, InStrRev(Path, "\") + 1)

 

[jaf]

Hi Rick,
Thanks. I've never new that one was there.

John