find in reverse
 

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

find in reverse
 

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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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 (MVP - Excel)


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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).

find in reverse
 

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

ROW(1:99)

<g

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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?

--
Rick (MVP - Excel)

find in reverse
 

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" wrote in message ...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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

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

--
Rick (MVP - Excel)


"jaf" wrote in message
...
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" wrote in message
...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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

find in reverse
 

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

John


"Rick Rothstein" wrote in message ...
You can use the InStrRev function to search backwards through text. To get
the filename from a path...

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

--
Rick (MVP - Excel)


"jaf" wrote in message
...
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" wrote in message
...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"smw" wrote in message
...
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