how to return the second from last word in a cell?

[maxmadx]

Hi there, I have 2 questions:

Can someone tell me

1. what formula will return the second from last word in a series of words in a cell, regardless of the number of words in it.

&

2. what is the formula to count the number of characters in a cell?


Thank you!

 

[Robert Mika]

Question 1:
http://www.ozgrid.com/News/jul-2005.htm


Question 2:
=LEN()

 

[Special-K99]

Re last but one word I can't get the solution on Robert's link to work.

If you're having the same trouble maxmadx then here's another solution, albeit a tad long

=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

 

[maxmadx]

Re last but one word I can't get the solution on Robert's link to work.

If you're having the same trouble maxmadx then here's another solution, albeit a tad long

=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

Special K to the rescue again, thank you!

 

[maxmadx]

Re last but one word I can't get the solution on Robert's link to work.

If you're having the same trouble maxmadx then here's another solution, albeit a tad long

=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

oops, actually, this seems to return the last word not the second last?

 

[maxmadx]

oops, actually, this seems to return the last word not the second last?

oo, i know why, its because the text had a blank space at the end- nevermind!

 

[barry houdini]

For second to last word try

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",40)),80),40))

 

[maxmadx]

Thank you very much to everyone who has helped me, I really appreciate all your help.