+ Reply to Thread
Results 1 to 7 of 7

automate cut and paste hyperlinks

  1. #1
    Registered User
    Join Date
    12-19-2015
    Location
    London, England
    MS-Off Ver
    Excel 2097
    Posts
    3

    automate cut and paste hyperlinks

    Hi,

    Col 1 Col 2

    hyperlink blank
    a number hyperlink
    text hyperlink

    What I want to do is to cut and paste the hyperlink from column 1 into Col 2. But I've got 11,000 records so I need to automate the process.

    So what I'm trying to say is 'if Col 1 contains a hyperlink, please cut that hyperlink and put it into Col 2 but if Col 1 contains anything other than a hyperlink, don't do anything.

    So I end up with:
    blank hyperlink
    a number hyperlink
    text hyperlink

    And the resultant hyperlink must work (in some of my experiments the url doesn't appear).

    I've basically copied and pasted a lot of records from a website and I'm now trying to get everything in the right columns.

    Any help appreciated.

    Thanks

    feynsteclay

  2. #2
    Registered User
    Join Date
    12-19-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    16

    Re: automate cut and paste hyperlinks

    Hope this helps.. Try using Hyperlink function

    =HYPERLINK ( link, [display_name] )

    in the place of "link", give the cell reference.. and "display_name" is optional..

    later drag the function to all the 11000 records you have.

  3. #3
    Registered User
    Join Date
    12-19-2015
    Location
    London, England
    MS-Off Ver
    Excel 2097
    Posts
    3

    Re: automate cut and paste hyperlinks

    Thanks but it highlights on of the problems I had when experimenting - the resultant 'hyperlink' is a piece of text that's underlined like a hypertext link but there's no address information behind it so when you click it it just says the address is not valid.

  4. #4
    Registered User
    Join Date
    12-19-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    16

    Re: automate cut and paste hyperlinks

    Hi -

    Try the code below.

    When the Macro is run, cell with hyperlink will be cut and paste the same to its corresponding column.

    Please Login or Register  to view this content.
    Hope this helps.

  5. #5
    Registered User
    Join Date
    12-19-2015
    Location
    London, England
    MS-Off Ver
    Excel 2097
    Posts
    3

    Re: automate cut and paste hyperlinks

    Hi thanks for you input. I can't use it because I'm not quite sure how to - I'm was hoping for a nested function type solution. But thanks.

  6. #6
    Registered User
    Join Date
    12-19-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    16

    Re: automate cut and paste hyperlinks

    Unfortunately.. as per my knowledge.. we dont have any such function in Excel which could extract the link from a hyperlink text/cell.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,431

    Re: automate cut and paste hyperlinks

    So what I'm trying to say is 'if Col 1 contains a hyperlink, please cut that hyperlink and put it into Col 2 but if Col 1 contains anything other than a hyperlink, don't do anything.
    As far as I know, there are no regular formulas that will "see" a hyperlink. Formulas work on what is displayed in the cell (the answers), and not the actual cell contents (formulas)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Automate repititive copy and paste
    By Raury in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2014, 12:53 PM
  2. How to automate copy paste
    By alyasch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2013, 09:36 AM
  3. How do I automate the validation of excel created Hyperlinks?
    By JPWRana in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2013, 05:08 PM
  4. Automate copy and paste based on condition
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-13-2012, 01:48 PM
  5. Automate Hyperlinks to Pictures
    By Nicholas Koretsky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2011, 05:02 AM
  6. Automate cut/paste functions - Help!
    By Greg in forum Excel General
    Replies: 2
    Last Post: 06-13-2005, 03:05 PM
  7. Automate Cut & Paste Rows to different tab in Workbook
    By Stacie Fugate in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2005, 02:06 PM

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