+ Reply to Thread
Results 1 to 4 of 4

Parsing HTML element into excel

  1. #1
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Parsing HTML element into excel

    Good afternoon all,

    I am trying to parse a section of html into excel but I don't know how to parse into a new row with each pattern rotation. I can do this both in Word and notepad++, but I can't find the same function of replace-with-line-break in excel's find-and-replace box.

    Original html extract:
    "shb":{"landline":"£1","mobile":"£1","text":"8p","mms":"24p"},"af":{"landline":"18p","mobile":"18p","text":"8p","mms":"24p"},"ak":{"landline":"3p","mobile":"3p","text":"8p","mms":"24p"},"al":{"landline":"10p","mobile":"25p","text":"8p","mms":"24p"},"dz":{"landline":"5p","mobile":"27p","text":"8p","mms":"24p"},

    Results I need:
    Capture.PNG

    There is only one line as such in the entire page, so I don't need to loop through "blocks" of html codes with multiple lines.

    I have had a look around and not sure what would be a good way to tackle this. RegEx (how to do it in excel macro)? HTML tags parsing (but these are not strictly tags)? I need to extract the correct section from page source as well, but I imagine this shouldn't be too hard. The url to the page I am extracting from is http://giffgaff.com/index/international

    Thank you very much for reading. Any help or suggestions is greatly appreciated!
    Last edited by chococ; 02-12-2014 at 07:17 AM.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Parsing HTML element into excel

    How's this as a starter for 10, needs the currency fixing though:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-14-2014
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Parsing HTML element into excel

    Kyle123 you are a life-saver! Yes that is exactly what I have been looking for. Just one little more help needed, if that is not to outrageous to ask: I need the results to paste from cell C2 onwards, is that possible? I have tried changing Range("A" into Range("C" and that worked (onto cell C1) but when I replaced with Range("C2" it didn't register the difference. A million thanks!!!

    Edit:
    And don't worry about the prices, I have sorted them out with find-and-replace:
    1. replace all p's with /100
    2. run =VALUE(LEFT(C2,FIND("/",C2)-1))/VALUE(RIGHT(C2,LEN(C2)-FIND("/",C2))) on all the prices to switch them into pounds
    Very grateful for your help

    Edit 2:
    I have played around with the Range and sorted it out. Sorry about the silly question. Thanks again!!
    Last edited by chococ; 02-11-2014 at 12:56 PM.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Parsing HTML element into excel

    Glad you got it sorted

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-24-2014, 09:00 PM
  2. HTML Mouseover Parsing into Excel
    By SimpleSimon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-26-2011, 04:01 PM
  3. Parsing an html address ?
    By mariom in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-24-2010, 07:53 AM
  4. Excel & HTML Parsing
    By Porphyria in forum Excel General
    Replies: 4
    Last Post: 06-23-2006, 07:25 PM
  5. [SOLVED] HTML parsing with VBA
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2006, 05:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1