Results 1 to 8 of 8

Thread: Extract text from URL

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract text from URL

    I have over 40,000 url similar to this one below


    https://i.postimg.cc/4xRS70r0/Anne-Hathaway-10.jpg


    What I want to do is create a listing title automatically from the above link so it reads:


    ANNE HATHAWAY A4 GLOSSY PHOTO PRINT #10 *FREE P&P*


    So basically I am taking the name and number from the URL.


    Any advice appreciated it is driving me insane.

  2. #2
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,481
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text from URL

    Let's suppose your URLs are in column A and you wish to create that string in column B.
    Try this macro:
    Code:
    Sub AnB()
    Dim FixStr As String, I As Long, mySplit
    Dim CRV, CExNum As String, cDash, oStr As String
    '
    FixStr = " A4 GLOSSY PHOTO PRINT ### *FREE P&P*"
    '
    For I = 1 To Cells(Rows.Count, "A").End(xlUp).Row                               'Scan each row
        CRV = UCase(Cells(I, "A").Value)
        If UCase(Left(CRV, 4)) = "HTTP" Then                                        'seams a Url
            mySplit = Split(CRV, "/", , vbTextCompare)                              'parse by /
            If UBound(mySplit) > 1 And Right(CRV, 4) = ".JPG" Then                  'enough items & jpg?
                cDash = InStrRev(mySplit(UBound(mySplit)), "-", , vbTextCompare)
                If cDash > 0 Then
                    oStr = Replace(FixStr, "###", "#" & Replace(Mid(mySplit(UBound(mySplit)), cDash + 1), ".jpg", "", , , vbTextCompare), , , vbTextCompare)
                    oStr = Left(mySplit(UBound(mySplit)), cDash - 1) & oStr
                    Cells(I, "B").Value = oStr
                End If
            End If
        End If
    Next I
    End Sub
    Insert the code in a Standard Module of your vba, for example in Module1; then, when the url list is ready, run Sub AnB:
    -from Excel, the right worksheet selected; press Alt-F8; select AnB from the list of available macros and press Run

    The Url list will be scanned, and the string shall be written in column B

    Bye
    Anthony (GMT+1)

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract text from URL

    Quote Originally Posted by Freddie Dogg View Post
    I have over 40,000 url ..
    In that case I think you will find this considerably faster to process.
    I have assumed the url's start in cell A2 also with results going in to column B

    Code:
    Sub ExtractFromURL()
      Dim a As Variant, b As Variant
      Dim urlBits(1 To 2) As String
      Dim i As Long
      
      Const strBase As String = " A4 GLOSSY PHOTO PRINT #@ *FREE P&P*"
      
      a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
      ReDim b(1 To UBound(a), 1 To 1)
      For i = 1 To UBound(a)
        urlBits(2) = Replace(Mid(a(i, 1), InStrRev(a(i, 1), "-") + 1), ".jpg", "", 1, 1, 1)
        urlBits(1) = Replace(Mid(a(i, 1), InStrRev(a(i, 1), "/") + 1), "-" & urlBits(2) & ".jpg", "", 1, 1, 1)
        If IsNumeric(urlBits(2)) Then b(i, 1) = UCase(urlBits(1) & Replace(strBase, "@", urlBits(2)))
      Next i
      Range("B2").Resize(UBound(b)).Value = b
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    Oct 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text from URL

    Quote Originally Posted by Peter_SSs View Post
    In that case I think you will find this considerably faster to process.
    I have assumed the url's start in cell A2 also with results going in to column B

    Code:
    Sub ExtractFromURL()
      Dim a As Variant, b As Variant
      Dim urlBits(1 To 2) As String
      Dim i As Long
      
      Const strBase As String = " A4 GLOSSY PHOTO PRINT #@ *FREE P&P*"
      
      a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
      ReDim b(1 To UBound(a), 1 To 1)
      For i = 1 To UBound(a)
        urlBits(2) = Replace(Mid(a(i, 1), InStrRev(a(i, 1), "-") + 1), ".jpg", "", 1, 1, 1)
        urlBits(1) = Replace(Mid(a(i, 1), InStrRev(a(i, 1), "/") + 1), "-" & urlBits(2) & ".jpg", "", 1, 1, 1)
        If IsNumeric(urlBits(2)) Then b(i, 1) = UCase(urlBits(1) & Replace(strBase, "@", urlBits(2)))
      Next i
      Range("B2").Resize(UBound(b)).Value = b
    End Sub
    This works fantastic, thank you. However just realised that there are some PNG images too, can you amend the code please?

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract text from URL

    Quote Originally Posted by Freddie Dogg View Post
    However just realised that there are some PNG images too, can you amend the code please?
    Does this modification work for you?

    Code:
    urlBits(2) = Replace(Mid(a(i, 1), InStrRev(a(i, 1), "-") + 1), ".jpg", "", 1, 1, 1)
    urlBits(2) = Split(Mid(a(i, 1), InStrRev(a(i, 1), "-") + 1), ".")(0)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Oct 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text from URL

    Hi Peter, I am getting a Run-time error '9': Subscript out of range when I replace with modification above.

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract text from URL

    Quote Originally Posted by Freddie Dogg View Post
    Hi Peter, I am getting a Run-time error '9': Subscript out of range when I replace with modification above.
    When you get the error, click Debug and hover your cursor over the 'a' in a(i,1) in the yellow line.
    What is the url displayed?

    Alternatively, hover over the 'i' and if that was, say 10, look at row 11 in your sheet to check that url.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,315
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract text from URL

    There is a formula solution possible if you are interested in one...
    Code:
    =SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(UPPER(A1),LEN(A1)-4),"/",REPT(" ",300)),300)),"-"," ",1),"-"," A4 GLOSSY PHOTO PRINT #")&" *FREE P&P*"
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •