A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

find the last occurance of a character in a cell



 
 
Thread Tools Display Modes
  #1  
Old April 3rd 10, 08:54 PM posted to microsoft.public.excel.misc
Tonso
external usenet poster
 
Posts: 86
Default 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
Ads
  #3  
Old April 3rd 10, 09:28 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default 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



  #4  
Old April 3rd 10, 09:41 PM posted to microsoft.public.excel.misc
Tonso
external usenet poster
 
Posts: 86
Default 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
  #5  
Old April 3rd 10, 09:52 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,504
Default 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
> .
>

  #6  
Old April 3rd 10, 09:54 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,504
Default 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
> > .
> >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find first character in cell mahmad Excel Worksheet Functions 10 October 2nd 08 05:03 PM
Find the next occurance Lou Excel Worksheet Functions 6 April 8th 08 04:37 AM
find a cell with a character Dan Kowalski Excel Worksheet Functions 1 February 28th 08 05:00 PM
Find the 1st occurance of a number in a cell lovemuch Excel Worksheet Functions 4 August 17th 06 01:02 AM
Find last occurance of character in text string JDay01 Excel Worksheet Functions 2 February 14th 06 04:29 PM


All times are GMT +1. The time now is 02:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.