Need help on regular expression, where we are trying to grab number values as
well as minus sign if negative. Currently we can grab all the numbers
correctly, just if numbers are negative, we only grab the number not the
number and the minus sign:

Example for:
-10 we get 10 but want -10 of course

I am new to Regular Expressions and have played with the syntax but cannot
get it work properly. Currently we are using
"\D+(\d+)"

On Thu, 7 Jan 2010 23:27:01 -0800, Jason
wrote:

Need help on regular expression, where we are trying to grab number values as
well as minus sign if negative. Currently we can grab all the numbers
correctly, just if numbers are negative, we only grab the number not the
number and the minus sign:

Example for:
-10 we get 10 but want -10 of course

I am new to Regular Expressions and have played with the syntax but cannot
get it work properly. Currently we are using
"\D+(\d+)"

I note that your expression will only capture integers. And also will not
capture an integer at the start of a line.

For integers with an optional sign, and to allow capture of positive integers
at the beginning of a line, try:

"[-+]?\b\d+\b"
--ron

"[-+]?\b\d+\b"

This doesn't seem to grab any numbers at all. Perhaps there is more info I
can provide from the script below:

c.Offset(0, i + 1).Value = RegexMid(myStr, sURLdate, "bl gb")
c.Offset(0, i + 2).Value = RegexMid(myStr, sURLdate, "br gb")
c.Offset(0, i + 3).Value = RegexMid(myStr, sURLdate, "class=gb")

Private Function RegexMid(s As String, sDate As String, sTempType As String)
As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.MultiLine = True
re.Global = True
re.Pattern = "\b" & sDate & "/DailyHistory[\s\S]+?" & sTempType & "\D+(\d+)"

If re.test(s) = True Then
Set mc = re.Execute(s)
RegexMid = mc(0).submatches(0)
End If
Set re = Nothing
End Function


***Here is an example of the data from the source page that we are trying to
pull from

</tr
</tbody
<tbody
<tr
<td<a href="/history/airport/KSTP/2010/1/1/DailyHistory.html"1</a</td
<td class="bl gb"
8
</td
<td class="gb"
0
</td
<td class="br gb"
-8
</td

Thank you!

I put the string into a file c:\temp\test.txt then ran this code. the
pattern I was using worked. You have to move sequentially through the
file to get the data. Look at what I did below.


Sub UseScript()


Const ReadFile = "c:\temp\test.txt"

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateFalse)

MyStr = fin.readall
fin.Close
URLdate = DateValue("1/1/2010")
sURLdate = Format(URLdate, "/yyyy/d/m")

Set c = Range("A1")

'find html header
FindStr = "/history/airport/KSTP" & sURLdate & "/DailyHistory.html"
StartSearch = 1
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(SearchStr, FindStr)
If IsNull(Results) Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If

'find Bl gb number
Firstbyte = Results.Item(0).firstIndex
NumberBytes = Results.Item(0).Length
FindStr = "bl gb"
StartSearch = StartSearch + Firstbyte + NumberBytes
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(SearchStr, FindStr)
If IsNull(Results) Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If
Firstbyte = Results.Item(0).firstIndex
NumberBytes = Results.Item(0).Length
FindStr = "(\d+|([-+]\d+))"
StartSearch = StartSearch + Firstbyte + NumberBytes
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(Mid(MyStr, StartSearch), FindStr)
If IsNull(Results) Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If
c.Offset(0, i + 1).Value = Results.Item(0).Value

'find class=gb number


Firstbyte = Results.Item(0).firstIndex
NumberBytes = Results.Item(0).Length
FindStr = "class=""gb"""
StartSearch = StartSearch + Firstbyte + NumberBytes
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(SearchStr, FindStr)
If IsNull(Results) Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If
Firstbyte = Results.Item(0).firstIndex
NumberBytes = Results.Item(0).Length
FindStr = "(\d+|([-+]\d+))"
StartSearch = StartSearch + Firstbyte + NumberBytes
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(Mid(MyStr, StartSearch), FindStr)
If IsNull(Results) Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If
c.Offset(0, i + 2).Value = Results.Item(0).Value

'find Br gb number

Firstbyte = Results.Item(0).firstIndex
NumberBytes = Results.Item(0).Length
FindStr = "Br gb"
StartSearch = StartSearch + Firstbyte + NumberBytes
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(SearchStr, FindStr)
If Results Is Nothing Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If
Firstbyte = Results.Item(0).firstIndex
NumberBytes = Results.Item(0).Length
FindStr = "(\d+|([-+]\d+))"
StartSearch = StartSearch + Firstbyte + NumberBytes
SearchStr = Mid(MyStr, StartSearch)
Set Results = RegexMid(Mid(MyStr, StartSearch), FindStr)
If Results Is Nothing Then
MsgBox ("Did not find string : " & FindStr)
Exit Sub
End If
c.Offset(0, i + 3).Value = Results.Item(0).Value




End Sub



Private Function RegexMid( _
ByVal s As String, _
ByVal FindStr) As Variant

Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.MultiLine = True
re.Global = True
re.Pattern = FindStr

If re.test(s) = True Then
Set mc = re.Execute(s)
Set RegexMid = mc
Else
Set RefexMid = Nothing
End If

Set re = Nothing
End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168082

Microsoft Office Help

Hello Dan,

Based on my researches, if we want to identify the negative number, we can
use the following regular expressions,
^(-)?\D+(\d+)

Best regards,
Ji Zhou
Microsoft Online Community

And for more Regular Expression pattern, as well as a list of symbol usage,
you can refer the following MSDN link,
http://msdn.microsoft.com/en-us/library/ms974570.aspx

Hope this helps!


Best regards,
Ji Zhou
Microsoft Online Community Support

Neither of these suggestions would grab the negative sign, so maybe there is
more to it. I have posted what I believe to be the more relevant portions of
the code below:

c.Offset(0, i + 1).Value = RegexMid(myStr, sURLdate, "bl gb")
c.Offset(0, i + 2).Value = RegexMid(myStr, sURLdate, "br gb")
c.Offset(0, i + 3).Value = RegexMid(myStr, sURLdate, "class=gb")

Private Function RegexMid(s As String, sDate As String, sTempType As String)
As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.MultiLine = True
re.Global = True
re.Pattern = "\b" & sDate & "/DailyHistory[\s\S]+?" & sTempType & "\D+(\d+)"

If re.test(s) = True Then
Set mc = re.Execute(s)
RegexMid = mc(0).submatches(0)
End If
Set re = Nothing
End Function


***Here is an example of the data from the source page that we are trying to
pull from

</tr
</tbody
<tbody
<tr
<td<a href="/history/airport/KSTP/2010/1/1/DailyHistory.html"1</a</td
<td class="bl gb"
8
</td
<td class="gb"
0
</td
<td class="br gb"
-8
</td

Thank you!

You need to OR to expressions like I did below

"(\d+|([-+]\d+))"

the pipe character is an or function to either have the minus sign or
not have the minus sign


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168082

Microsoft Office Help

Joel,

I tried using your suggestion, but it won't pull any numbers, much less the
negatives.

The closest I got was using:
"(\D+(-)?\d+)"

but it pulls
"-8
instead of
-8

or
-8
instead of
-8

"joel" wrote:


You need to OR to expressions like I did below

"(\d+|([-+]\d+))"

the pipe character is an or function to either have the minus sign or
not have the minus sign


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168082

Microsoft Office Help

.

I don't know what the \D (capital D does).

"(\D+(-)?\d+)"

1) \d+ the backslash d say take one digit, the plus says take all
didgits folowing the first digit (digit is 0 to 9)

2) ? says match any character before the string of digits 0 to 9.

3) "(\d+|([-+]\d+))" the first part is the same as the \d+ above. I
added the pipe to have an Or'd conditions. Te square brackets say to
match either character the plus sign or the minus sign.


I think you have a space in front of the minus sign. Then you need
this

"(\d+|([-+]\b\d+))"

You could also try this which will accept either a space between the
plus and minus sign of no space.

"(\d+|([-+]\b\d+))|([-+]\d+)"


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168082

Microsoft Office Help

On Fri, 8 Jan 2010 10:41:01 -0800, Jason
wrote:

Joel,

I tried using your suggestion, but it won't pull any numbers, much less the
negatives.

The closest I got was using:
"(\D+(-)?\d+)"

but it pulls
"-8
instead of
-8

or
-8
instead of
-8

I'm just curious about what happened with my suggestion?

"[-+]?\b\d+\b"
--ron