Try this:
'**********************************************
'Returns the iNum'th whole number from sText
'Eg: =GetNumber("10 or maybe 45 employees",2)
Function GetNumber(sText As String, iNum As Integer) As Variant
Static regEx As Object
Dim m
Dim i As Integer
If regEx Is Nothing Then
Set regEx = CreateObject("vbscript.regexp")
regEx.Pattern = "(\d+)"
regEx.Global = True
regEx.IgnoreCase = True
End If
Set m = regEx.Execute(sText)
If m.Count >= iNum Then
GetNumber = m(iNum - 1)
Else
GetNumber = ""
End If
End Function
'**********************************************
Tim
"crazy_vba" <
[email protected]> wrote in message
news:
[email protected]...
>
> Hey Folks !
> Here comes back the Crazy_vba that I am.. and still working on his code
> :p
>
> Thanks to the previous help, and the download of a pack called
> morefunc, I can get more advanced and easy coding now. (the website is
> http://xcell05.free.fr/)
>
> Anyway...Here is my **new** problem.
>
> I got in one cell this sentence (string):
>
> B16 : "Franchisor is a public company. Franchisor has 120 employee(s);
> 59 employee(s) in franchise department"
>
> I've tried in the past days, and thanks to your answers to extract the
> specific numbers 120 and 59... I used the Range("e14").Value =
> Val(Right(Range("b16").Value, 38)) and Range("d14").Value =
> Val(Right(Range("b16").Value, 55)).
>
> But, cos there's a But ! This method was not working as on some
> downloaded webpages, the sentence was longer or shorter...so the 38 and
> 55 values were not returning the info that I needed.
>
> I'm now using a more powerfull technique, with the function STEXTE=
> -included in the morefunc pack. Basically, I give it the cell in which
> I'm working, the number of words I'm looking for and their
> positions...
>
> [ "=STEXTE(RC[-1],i,1)" ] where RC[-1] is the cell I'm looking in; i is
> the ith word position in the cell; 1 is the number of words i'm looking
> for, here only one "word".
>
> So let's go back to our cell B16:
> "Franchisor is a public company. Franchisor has 120 employee(s); 59
> employee(s) in franchise department"
>
> basically, with the STEXTE function, if I want to get the number 120
> and 59, I will need to type the following formula:
> =STEXTE(B16;8;1) to get 120 out; and
> =STEXTE(B16;10;1) to get 59 out.
>
> alright so far? But you would tell me: Hey Crazy_Vba, You will still
> have the same problem if the NUMBERS 120 and 59 are not at the 8th/10th
> place in your sentence in the case of another company.
> I would say: You're right sir !
>
> That is why I've tried to code this was my macro:
>
> For i = 1 To 50
> ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"
> test = ActiveCell.Value
>
> If VarType(test) = vbInteger Then
> ActiveCell.Offset(0, 1).Select
> ActiveCell.Value = "Integer"
> End If
>
> If VarType(test) = vbString Then
> ActiveCell.Offset(0, 1).Select
> ActiveCell.Value = "String"
> End If
> Next i
>
> My For ... Next is not "perfect" yet but it is not my problem (yet!)
> As you have understood by reading my code, I'm taking each word one by
> one (hence the for next) and "test" it with a If...End If procedure to
> know if it is a String or an Integer.
>
> However, by doing a step-by-step run of my macro, it does select each
> word one by one, display it, and check if it is a string or an
> integer... BUT IT IS ALWAYS DEFINED AND UNDERSTOOD AS A STRING ! Even
> if the "extracted word" is 120.
>
> So Here is my idea: to insert in my code a Convertion Phase, that will
> "try" to convert the extracted word into integer, and then, if it is an
> integer, the macro should stop!
>
> What I'm looking for would be something like that :
>
> For i = 1 To 50
> ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"
>
> TRANSFORM ACTIVECELL.VALUE INTO INTEGER
>
> test = ActiveCell.Value
>
> If VarType(test) = vbInteger Then
> STOP MACRO
>
> If VarType(test) = vbString Then
> CONTINUE TO LOOK FOR THE OTHER WORDS IN THE CELL
> End If
> Next i
>
> Any idea ? :-)
> I'm dead, I've been looking thoughout the forum with key words like
> "convert strings integer double", even on google, but cannot find
> exactly what I need!
>
> how to convert a damn "120" defined as a string into a "120" defined as
> integer ! :-) and cook it with a nice For...Next procedure !
>
> Thanks in advance for your consideration and help guys!
> Crazy Vba
>
>
> --
> crazy_vba
> ------------------------------------------------------------------------
> crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679
> View this thread: http://www.excelforum.com/showthread...hreadid=539716
>
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Tim Williams
Bookmarks