How to find the last occurence of a character

Hi,

I'd like to find the last occurence of a given character in a String.

For example, if cell A1 contains This.Package.Name.Class, I'd like to find the last period position (18).

The text function FIND doesn't permit that and I can't find a text function that does a reverse FIND or a text function that can reverse the content of a cell before FINDing things in it.

Thanks,

Mike.

Hi

Do you want to return the text after it? If so, try this:
=MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+
1,50)
This will return the 50 characters after the last dot.

--
Andy.


"Mike" wrote in message
...
Hi,

I'd like to find the last occurence of a given character in a String.

For example, if cell A1 contains This.Package.Name.Class, I'd like to find
the last period position (18).

The text function FIND doesn't permit that and I can't find a text
function that does a reverse FIND or a text function that can reverse the
content of a cell before FINDing things in it.

Thanks,

Mike.

Place the function below in a general module

Function findit(c As Range)
findit = Len(Left(c, Len(c) - Application.Find(".", StrReverse(c), 1) + 1))
End Function


Assuming the text is in cell A1, in another cell enter =findit(A1) to
return the position of the last period in cell A1. [You may need Excel 2002
or later]

--
XL2002
Regards

William



"Mike" wrote in message
...
| Hi,
|
| I'd like to find the last occurence of a given character in a String.
|
| For example, if cell A1 contains This.Package.Name.Class, I'd like to find
the last period position (18).
|
| The text function FIND doesn't permit that and I can't find a text
function that does a reverse FIND or a text function that can reverse the
content of a cell before FINDing things in it.
|
| Thanks,
|
| Mike.

Hi Mike,

One way, with your string in A1, this Array formula (Ctrl-Shift-Array):

=MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".", ROW(INDIRECT("1:"&LEN(A1)))))

Regards,

Daniel M.

"Mike" wrote in message
...
Hi,

I'd like to find the last occurence of a given character in a String.

For example, if cell A1 contains This.Package.Name.Class, I'd like to find the
last period position (18).

The text function FIND doesn't permit that and I can't find a text function
that does a reverse FIND or a text function that can reverse the content of a
cell before FINDing things in it.

Thanks,

Mike.

"Daniel.M" wrote...
One way, with your string in A1, this Array formula (Ctrl-Shift-Array):

=MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="." ,ROW(INDIRECT("1:"&LEN(A1)))))

Shorter alternative, also an array formula,

=LEN(A1)+1-MATCH(".",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1),0)

The MAX formula returns 0 if no match is found. The LEN-MATCH formula returns
#N/A. The latter could be adapted to return 0 as

=LEN(A1)+1-MATCH(".",MID("."&A1,LEN(A1)+2-ROW(INDIRECT("1:"&(LEN(A1)+1))),1),0)

--
To top-post is human, to bottom-post and snip is sublime.