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 fo
all the VBA addicts outhere ! not a big challenge, I guess, but stil
for me, it is. I've tried to search through the forum, but I didn'
find anything regarding my needs:

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

Thanks to your previous help, I've been able to extract by myself th
numbers 650 and 20, and put'em respectively in E14 and D14 by using i
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 4t
position of the sentence, here it is "privately-held".
I've tried with the same kind of formula (Val(Right ... etc) but I jus
got 0 and not the word itself >_<

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

Thanks for your future help!
VBA is Alive :-

 

[Don Guillett]

have a look at MID

 

[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
'-------------


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



|
| 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?action=getinfo&userid=33679
| View this thread: http://www.excelforum.com/showthread.php?threadid=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 t
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

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