0

I have a column in excel with unformatted image links. I've highlighted the image links in the raw data below

enter image description here

I need an excel VBA macro to convert data like so:

enter image description here

I wrote a regular expression http[s?]:\/\/.*(.png|.jpg) to pattern match the links. Sample:

enter image description here

I modified the function found here to do the processing

Function ExtractURL(ByVal text As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "(http[s?]:\/\/.*(.png|.jpg))"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(text)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0).submatches.Item(0)
End If

ExtractURL = result

End Function

How do I apply this function to replace the values in Column A?

EDIT: CLARIFICATION/CONTEXT

I have 1000+ image links. I simply showed 5 images to make the example straightforward. It needs to work only off of column A, since its part of a larger series of macros.

Improve this question
6
  • 1
    Have you tried adding an actual reference to the regex library, browse the typelib and see what members/methods are available to use? Commented Apr 8, 2018 at 18:51
  • What are you doing now and what is the problem Commented Apr 8, 2018 at 18:51
  • @RonRosenfeld Good point well made! Comment deleted. Commented Apr 8, 2018 at 19:01
  • Sorry I have no idea how to deploy it or what a UDF is, I don't write excel VBA that often Commented Apr 8, 2018 at 19:01
  • Put the code in a standard module then in a cell you put = ExtractURL(cellwheretextis) Commented Apr 8, 2018 at 19:02

4 Answers 4

Reset to default
2

If all you want is to replace column A with URLs only, you may try something like this...

Sub ExtractURL()
Dim lr As Long
Dim Rng As Range, Cell As Range
Dim RE As Object    

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A1:A" & lr)

Set RE = CreateObject("vbscript.regexp")

With RE
    .Pattern = "(http[s?]:\/\/.*(.png|.jpg))"
    .Global = False
    .IgnoreCase = True
End With

For Each Cell In Rng
    If RE.test(Cell.Value) Then
        Cell.Value = RE.Execute(Cell.Value)(0)
    End If
Next Cell
End Sub

How to install your new code:

  • Copy the Excel VBA code
  • Select the workbook in which you want to store the Excel VBA code
  • Press Alt+F11 to open the Visual Basic Editor
  • On VB Editor, choose Insert --> Module
  • Paste the copied code into the opened code window
  • Save your workbook as Macro-Enabled Workbook.

To run the Excel VBA code:

  • Press Alt+F8 to open Macro list

  • Select the macro ExtractURL

  • Click on Run.

Note: If you want to place the output in another column, say column B, use this line instead...

Cell.Offset(0, 1).Value = RE.Execute(Cell.Value)(0)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I've been always told that regexp slows things down

so here's a not-RegExp solution:

Sub main()
    With Range("A1", Cells(Rows.Count, 1).End(xlUp))
        .Replace what:="*https", replacement:="https", lookat:=xlPart
        .Replace what:=".JPG*", replacement:=".JPG", lookat:=xlPart
    End With
End Sub

and should you necessarily need a Function:

Function ExtractURL(text As String)    
    ExtractURL = Mid(Left(text, InStrRev(text, ".JPG", , vbTextCompare) + 3), InStr(1, text, "https", vbTextCompare))
End Function
Improve this answer

2 Comments

I'm not worried too much about slow performance, I'm more worried about easy - modular code to modify. Excel's built-in find/replace is kind of limited, regex is much more powerful and has better catch-alls
well, RegEx is as much powerful everything as not easy. Find() gets you the whole job done with 4 code lines and the Function job in one line. So, suit yourself. And, BTW, you are welcome.
0

from old instructions I once wrote

To enter a User Defined Function (UDF):

  • alt-F11 opens the Visual Basic Editor.
  • Ensure your project is highlighted in the Project Explorer window.
  • Then, from the top menu, select Insert/Module and paste the code into the window that opens.

To use this User Defined Function (UDF), enter a formula like ExtractURL(cell_ref) in some cell.

Improve this answer

3 Comments

Can you check my edit on comment? I needed the macro to run the UDF itself. I have about 1000 images in my file and once data gets processed another series of macros gets runned
Put your macro in the same module. Suggest you do a search for some elementary tutorials on using VBA.
Nevermind I figured it out already, its just been awhile since I did any excel VBA
0

Per my original post, this is what I used. With the extractURL function defined in my problem statement

Sub MainTest()

Range("A1").Activate
Do
    If ActiveCell.Value = "" Then Exit Do
    ActiveCell.Offset(1, 0).Activate
    argCounter = argCounter + 1
Loop

For row = 1 To argCounter + 1
    Cells(row, 1).Value = ExtractURL(Cells(row, 1).Value)
Next row

End Sub
  1. Dump code in module alt+f11
  2. Save
  3. View macro → MainTest
Improve this answer

1 Comment

since you use the function to process all column A cells, then you may want to check my answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.