find the last occurance of a character in a cell
 

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

find the last occurance of a character in a cell
 

Look in vba help index for INSTRREV
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

find the last occurance of a character in a cell
 

Try this...

All on one line.

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

--
Biff
Microsoft Excel MVP


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

find the last occurance of a character in a cell
 

On Apr 3, 4:28*pm, "T. Valko" wrote:
Try this...

All on one line.

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

--
Biff
Microsoft Excel MVP

"Tonso" wrote in message

...



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- Hide quoted text -

- 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

find the last occurance of a character in a cell
 

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.


"Tonso" wrote:

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
.

find the last occurance of a character in a cell
 

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.


"Mike H" wrote:

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.


"Tonso" wrote:

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
.