Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Marcro or formula to extract urls from a bunch of text in a column of cells

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Marcro or formula to extract urls from a bunch of text in a column of cells

    I have a column of cells over 2000 rows that have a bunch of text in each cell. In the middle of this text in each cell are websites (not hyperlinked). I was wondering if someone wrote a macro or formula to extract URLs located in a bunch of text? I can parse this out but doing some delimiting but that will take longer.

  2. #2
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,256
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    Can you post a sample of your data?
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  3. #3
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    Can you a list of examples: both the full text and the URL you want to grab.
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  4. #4
    New Member
    Join Date
    Nov 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    Text in cells:
    "AccessTurnkey Management Ataturk Cad. No. 72 Kozyatagi Istanbul 34736 P: +980 (216) 468 10 15 Firm Type: Asset Mangement Established: 1999 AccessTurnkey Management is an asset management firm that seeks hedge fund, venture capital, private equity, and real estate investments. www.accessturnkey.com The Firm's looks for growth equity investments and control buyouts of middle-market companies with operations and primary business activities. Prospective companies typically have annual revenues of $25 to $400 million and operating income of at least $2 million.

    URL to capture: www.accessturkey.com

    The urls typically start with www.. and not http:// . I don't think all of them are .com domain.

  5. #5
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    So you want to grab anything until you reach a space after the second . after the www?
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  6. #6
    New Member
    Join Date
    Nov 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    yes that would work.

  7. #7
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    Is this all in 1 cell?
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  8. #8
    New Member
    Join Date
    Nov 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    yes the text is all in one cell.

  9. #9
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    Code:
    Sub fff()
        For Each r In Columns(1).SpecialCells(2, 2)
            r.Offset(, 1) = "www" & Split(Split([A1], "www")(1))(0)
        Next
    End Sub
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  10. #10
    New Member
    Join Date
    Nov 2009
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marcro or formula to extract urls from a bunch of text in a column of cells

    thanks for the code...i'm a bit of a beginner, Dow I need to modify depending on what column the text is sitting in? in my case its in column G and I'd like the URls to go into column H.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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