Excel VBA - Replacing Values with a Regular Expression Function

Question

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

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

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

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.

Have you tried adding an actual reference to the regex library, browse the typelib and see what members/methods are available to use? — Mathieu Guindon, Apr 08 '18 at 18:51
Sorry I have no idea how to deploy it or what a UDF is, I don't write excel VBA that often — Vincent Tang, Apr 08 '18 at 19:01
Put the code in a standard module then in a cell you put = ExtractURL(cellwheretextis) — QHarr, Apr 08 '18 at 19:02
So in an empty cell when you start typing =Ex then function should appear. You simply need to complete the function call by putting a cell reference inside the () which contains the text you want to extract the url from. E.g. = ExtractURL(A1) — QHarr, Apr 08 '18 at 19:04

score 1 · Accepted Answer · answered Apr 08 '18 at 19:27

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)

DisplayName · Answer 2 · 2018-04-08T20:02:52.777

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

edited Apr 08 '18 at 20:02

answered Apr 08 '18 at 19:44

DisplayName

13,283
2
11
19

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 – Vincent Tang Apr 09 '18 at 18:55
1

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. – DisplayName Apr 10 '18 at 04:46

score 0 · Answer 3 · answered Apr 08 '18 at 19:04

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.

answered Apr 08 '18 at 19:04

Ron Rosenfeld

53,870
7
28
60

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 – Vincent Tang Apr 08 '18 at 19:14
Put your macro in the same module. Suggest you do a search for some elementary tutorials on using VBA. – Ron Rosenfeld Apr 08 '18 at 19:19
Nevermind I figured it out already, its just been awhile since I did any excel VBA – Vincent Tang Apr 08 '18 at 19:21

Vincent Tang · Answer 4 · 2018-04-08T19:36:50.927

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

Dump code in module alt+f11
Save
View macro → MainTest

edited Apr 08 '18 at 19:36

answered Apr 08 '18 at 19:31

Vincent Tang

3,758
6
45
63

since you use the function to process all column A cells, then you may want to check my answer – DisplayName Apr 08 '18 at 20:05

Excel VBA - Replacing Values with a Regular Expression Function

4 Answers4