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.

 find the last occurance of a character in a cell
 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

#1
April 3rd 10, 08:54 PM posted to microsoft.public.excel.misc
 Tonso external usenet poster Posts: 90
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
#2
April 3rd 10, 09:06 PM posted to microsoft.public.excel.misc
 Don Guillett[_2_] external usenet poster Posts: 1,522
find the last occurance of a character in a cell

#3
April 3rd 10, 09:28 PM posted to microsoft.public.excel.misc
 T. Valko external usenet poster Posts: 15,768
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
April 3rd 10, 09:41 PM posted to microsoft.public.excel.misc
 Tonso external usenet poster Posts: 90
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
April 3rd 10, 09:52 PM posted to microsoft.public.excel.misc
 Mike H external usenet poster Posts: 11,501
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
April 3rd 10, 09:54 PM posted to microsoft.public.excel.misc
 Mike H external usenet poster Posts: 11,501
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 11:40 AM.