+ Reply to Thread
Results 1 to 6 of 6

Macro to extract titles, dates, and hyperlinks from ranges and paste in new columns

  1. #1
    Registered User
    Join Date
    11-15-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Macro to extract titles, dates, and hyperlinks from ranges and paste in new columns

    Hi All:

    I'm new to macro programming, so please excuse me if this issue is an easy one to resolve. I have a spreadsheet with a couple hundred ranges describing web postings. The original file was a text file, which I saved into Excel. Each range looks like this:


    AUTHOR: Michael Smith
    TITLE: “Blah Blah Blah"
    STATUS: Draft
    ALLOW COMMENTS: 0
    CONVERT BREAKS: wysiwyg
    ALLOW PINGS: 0
    BASENAME: more-blah
    CATEGORY: Partners & Affiliates

    DATE: 11/11/2010 10:33:17 PM
    -----
    BODY:
    <p>Christmas is upon us...<a href="http://www.failblog.org/</a>.</p>

    I need to write a macro that will identify and paste the title, date, and link into columns on a different worksheet. Preferably, it would only be two columns, with the link hyperlinked in the title.

    So far I've tried to record me copy and pasting stuff from the cells, but that proved to be a failure, as did any code I tried to write. Any help would be greatly appreciated.

    Best,

    Josh
    Last edited by jswiss; 11-16-2010 at 10:51 AM.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Macro to extract titles, dates, and hyperlinks from ranges and paste in new colum

    jswiss,

    Welcome to the Excel Forum.

    It would help if we could see more of your raw data.

    Please attach your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

    To attach your workbook, click on the New Post button, then scroll down and click on the Manage Attachments button.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to extract titles, dates, and hyperlinks from ranges and paste in new colum

    Assuming those strings are all in column A in an ongoing list... try this:
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    11-15-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to extract titles, dates, and hyperlinks from ranges and paste in new colum

    A sample file is attached. The first tab, labeled "Blog Export 11-15-10" continues on more postings in the A column, but you can get the idea from what I posted.

    In the After tab is a sample of what I want it to look like.

    I also ran JBeaucaire's Macro, which captured the date, but was unable to grab the title and hyperlink.

    Thanks for all the help!
    Attached Files Attached Files

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to extract titles, dates, and hyperlinks from ranges and paste in new colum

    Hello jswiss,

    The attached workbook has a button on the "After" sheet to run the macro below. Will will extract the data as you have described.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  6. #6
    Registered User
    Join Date
    11-15-2010
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Smile Re: Macro to extract titles, dates, and hyperlinks from ranges and paste in new colum

    Leith, you are amazing! Thanks so much for all your help! I look forward to parsing through your code tomorrow to figure out how you did it!

    Thanks again,

    Josh

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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