t = Range("b14").Value
Msgbox Split(t, " ")(3)

....of course all of your "words" must be delimited by a single space for this to work.

For more powerful work you would need to investigate regular expression (see RegExp object).


Tim


"crazy_vba" wrote in message
...

Hello everyone!

Your last msgs really helped me out. But now here is a new trouble for
all the VBA addicts outhere ! not a big challenge, I guess, but still
for me, it is. I've tried to search through the forum, but I didn't
find anything regarding my needs:

here is what's inside cell B14:
" Franchisor is a privately-held company with 650 employee(s); 20
employee(s) in franchise department"

Thanks to your previous help, I've been able to extract by myself the
numbers 650 and 20, and put'em respectively in E14 and D14 by using in
my macro:

Range("e14").Value = Val(Right(Range("b14").Value, 38))
Range("d14").Value = Val(Right(Range("b14").Value, 55))

However, I would like now to have in C14 the "word" which is in 4th
position of the sentence, here it is "privately-held".
I've tried with the same kind of formula (Val(Right ... etc) but I just
got 0 and not the word itself _<

How can I get out the 4th word of this cell? (in some cases, it won't
be privately-held, but independant etc etc: I just know that the
sentence will always be like "Franchisor is a ......... company" ?

Thanks for your future help!
VBA is Alive :-)


--
crazy_vba
------------------------------------------------------------------------
crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679
View this thread: http://www.excelforum.com/showthread...hreadid=535220