find the last occurance of a character in a cell

[Tonso]

I can use the Find function to find the 1st occurance of a charater in
a cell. But, how to i find the last occurence of a character. in this
case, the i am looking for " ", or <space>. The length of the string
can vary, and the number of spaces can vary also.
example:
"Get dog food 55"
There are 14 spaces (1+1+12). i am intersted in the location of the
last, or 14th, space, the one that precedes the number 5


Thanks,

Tonso

 

[Don Guillett]

Look in vba help index for INSTRREV

 

[T. Valko]

Try this...

All on one line.

=FIND("^^",SUBSTITUTE(A1," ",
"^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

 

[Tonso]

Try this...

All on one line.

=FIND("^^",SUBSTITUTE(A1," ",
"^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

--
Biff
Microsoft Excel MVP






- Show quoted text -

Don, Biff, Paul,

Thank you all so very much! I do not know what I would do without this
group. You all increase the value of Excel many times over!!

Thansk again,
Tonso

 

[Mike H]

Hi,

You posted in programming but I think this is a worksheet method your after.

The ARRAY formula returns the position of that last space in a string so for
the character after you would add +1 at the end

=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)="
")*ROW(INDIRECT("A1:A"&LEN(A1))))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

You posted in programming

No you didn't, I had an elderly moment
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.