Extract the n word of a sentence in a cell

[crazy_vba]

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 :-)

[Don Guillett]

have a look at MID

--
Don Guillett
SalesAid Software
[email protected]
"crazy_vba" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Jim Cone]

Note that your method to find the numerics in the string will
fail when the number of characters in the string changes.

Two possibilities for extracting your phrase are shown.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'Works with all XL versions and works with multiple words.
Sub WhatsThere()
Dim strText As String
strText = Range("B14").Text
Select Case True
Case InStr(1, strText, "independent")
Range("C14").Value = "independent"
Case InStr(1, strText, "privately-held")
Range("C14").Value = "privately-held"
Case InStr(1, strText, "two words")
Range("C14").Value = "two words"
Case InStr(1, strText, "charitable")
Range("C14").Value = "charitable"
'more cases
Case Else
Range("C14").Value = "unknown"
End Select
End Sub

'Does not work with XL 97 and extracts a single word only.
Sub WhatsThere2()
Dim x As Variant
Dim strText As String
strText = Range("B14").Value
x = Split(strText)
Range("C14").Value = x(3)
End Sub
'-------------


"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 :-)

[Niek Otten]

You don't need VBA; this is a worksheet formula:

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",3))+1,FIND("@",SUBSTITUTE(A1," ","@",4))-FIND("@",SUBSTITUTE(A1," ","@",3)))


--
Kind regards,

Niek Otten



"crazy_vba" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|

[crazy_vba]

Note that your method to find the numerics in the string will
fail when the number of characters in the string changes.

You're exactly right. It didn't work few minutes ago...If i want it to work, I have to change the value indicating the n° of characters... damn.

what can I do ? >_<

[Jim Cone]

Well, you could tell us what version of XL your are using.
You could also tell us how/if the proposed solutions to
your original question worked.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"crazy_vba"
wrote in message

Jim Cone Wrote:
> Note that your method to find the numerics in the string will
> fail when the number of characters in the string changes.
>

You're exactly right. It didn't work few minutes ago...If i want it to
work, I have to change the value indicating the n° of characters...
damn.
what can I do ? >_<

[Tim Williams]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>