Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract the n word of a sentence in a cell
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 :- -- crazy_vb ----------------------------------------------------------------------- crazy_vba's Profile: http://www.excelforum.com/member.php...fo&userid=3367 View this thread: http://www.excelforum.com/showthread.php?threadid=53522 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract the n word of a sentence in a cell
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 :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract the n word of a sentence in a cell
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" 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 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract the n word of a sentence in a cell
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 t work, I have to change the value indicating the n° of characters.. damn. what can I do ? _ -- crazy_vb ----------------------------------------------------------------------- crazy_vba's Profile: http://www.excelforum.com/member.php...fo&userid=3367 View this thread: http://www.excelforum.com/showthread.php?threadid=53522 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract the n word of a sentence in a cell
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 ? _< |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract the n word of a sentence in a cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to copy the first word or two words from a cell containing a complete sentence to another cell | Excel Discussion (Misc queries) | |||
Find a word in a sentence | Excel Worksheet Functions | |||
Link a cell to a word in a sentence | Excel Discussion (Misc queries) | |||
Extract all letters from a cell sentence | Excel Worksheet Functions | |||
Extract the first word from a cell? | Excel Discussion (Misc queries) |