Prev: sorting two columns of merged cells
Next: Sorting and coloring cells, formula or macro help needed
From: Mike H on 3 Apr 2010 16:52
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
> .
>
From: Mike H on 3 Apr 2010 16:54
> 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
> > .
> >
First  |  Prev  | 
Pages: 1 2
Prev: sorting two columns of merged cells
Next: Sorting and coloring cells, formula or macro help needed