Solved

Regular expression to extract a string

Posted on 2014-09-12
15
200 Views
Last Modified: 2014-09-17
I need to extract 1.50 in this string from the source code of the web page, or the entire line and then I'll parse it down to the 1.50.
<\/span>\n\n\n"},"MNN$":{"9303565":"$1.50"},"WARRANTY":{}}),

I am using Excel VBA.

Here is the function

Sub ExtractData()

Dim regEx
Dim i As Long
Dim pattern As String
Set regEx = CreateObject("VBScript.RegExp")
regEx.IgnoreCase = True
regEx.Global = True
regEx.pattern = "[what do I put in here?]"


End Sub
0
Comment
Question by:rrhandle8
  • 8
  • 7
15 Comments
 

Author Comment

by:rrhandle8
Comment Utility
I should have add this clue. The string below is unique in the document

"MNN$":{"9303565":"$1.50"}
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
include the quotes in the expression.  You will need to double the quotes inside a string.
"$(\d+\.\d\d)"

Open in new window

0
 

Author Comment

by:rrhandle8
Comment Utility
First I need to extract the entire line from the html document.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
no, you don't
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
If you need just that one item, among many similar items, use this pattern
{"9303565":"$(\d+\.\d\d)"}

Open in new window

0
 

Author Comment

by:rrhandle8
Comment Utility
OK. Thanks. I will try it.
0
 

Author Comment

by:rrhandle8
Comment Utility
regEx.pattern = "{"9303565":"$(\d+\.\d\d)"}"

Expect end of statement error
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
As I stated earlier, in order for a string literal to contain quote characters, the internal quote characters need to be doubled.
regEx.pattern = "{""9303565"":""$(\d+\.\d\d)""}"

Open in new window

0
 

Author Comment

by:rrhandle8
Comment Utility
That didn't work either.  
The 9303565 is a unique number, so I can understand why it does work on the items I an feeding.
The line would be like "MNN$":{"9303565":"$1.50"} where 9303565 changes, then the MNN$":{ is unique within the document.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
That didn't work either.
Are you still getting an error message?
What string are you using for your pattern matching?  Are you sure it contains the 9303565 data?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I'm stepping away from the keyboard for a while.
0
 

Author Comment

by:rrhandle8
Comment Utility
9303565 is a unique number that changes on each web page.  I am looking for the $1.50 that is to the right of it.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
the 1.50 will be in submatches(0)

If the number changes, then I should be able to alter the pattern to work in the general case if you tell me more about those values.
0
 

Author Comment

by:rrhandle8
Comment Utility
aikimark,  Thanks for the help.  I solved the problem using a different technique, but I would like to know how to do this with regular expressions.

There is only one line in the html that contains MNN$":{"9303565":"$1.50"}
The only thing that changes is the long number in the middle and the price.
There is always a long number in the middle, and a price at the end.
I have discovered that I need to extract long number in the middle and the price.
The regular expression should return (in this case) 9303565 and 1.50.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
If it is the only price (currently formatted) value in these web pages, you can use the original pattern I posted. http:#a40319764


If you need to expand the match out to the curly brackets, you can use a modified version of what I posted in this comment: http:#a40320039
regEx.pattern = "{""\d+"":""$(\d+\.\d\d)""}"

Open in new window

In both cases, Submatches(0) contains the data you seek.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now