Prev: sorting two columns of merged cells
Next: Sorting and coloring cells, formula or macro help needed
From: Tonso on 3 Apr 2010 15:54
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: Don Guillett on 3 Apr 2010 16:06

Look in vba help index for INSTRREV
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Tonso" <wthomasss(a)hotmail.com> wrote in message
news:b7128322-d161-464a-be4a-7575cbabc65a(a)10g2000yqq.googlegroups.com...
>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: Paul on 3 Apr 2010 16:22

Perhaps try:

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


--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192727

http://www.thecodecage.com/forumz

From: T. Valko on 3 Apr 2010 16:28
Try this...

All on one line.

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

--
Biff
Microsoft Excel MVP


"Tonso" <wthomasss(a)hotmail.com> wrote in message
news:b7128322-d161-464a-be4a-7575cbabc65a(a)10g2000yqq.googlegroups.com...
>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: Tonso on 3 Apr 2010 16:41
On Apr 3, 4:28 pm, "T. Valko" <biffinp...(a)comcast.net> wrote:
> Try this...
>
> All on one line.
>
> =FIND("^^",SUBSTITUTE(A1," ",
> "^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
>
> --
> Biff
> Microsoft Excel MVP
>
> "Tonso" <wthoma...(a)hotmail.com> wrote in message
>
> news:b7128322-d161-464a-be4a-7575cbabc65a(a)10g2000yqq.googlegroups.com...
>
>
>
> >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
 |  Next  |  Last
Pages: 1 2
Prev: sorting two columns of merged cells
Next: Sorting and coloring cells, formula or macro help needed