Excel Macro to find text in cell and insert hyperlink on cell

Question

We're managing some system bugs in a web system and setting priority for execs in a spreadsheet. Each of the tickets has a "FD-" and four numbers as the ID. The web system has a hyperlink that has that "FD-####" at the end of the link. The end result would look like this -- http://www.mytickets.com/FD-####

I'd like to run a macro that finds all the FD-#### and inserts a hyperlink on each.

There may be multiple FD-#### in a single cell and there will certainly be other text in there.

I'd go through each and add the link but there are over 150 or so.

Thanks!

Answer 1

As mentioned in a comment, Excel doesn't seem to support multiple hyperlinks in a cell.

The code below will do the replacement from ticket to link:

Option Explicit

Sub loop_over_cells()
    Dim a_cell
    Dim replaced As String

    For Each a_cell In ActiveSheet.UsedRange
        Debug.Print "old value " & a_cell.Value
        replaced = RegexReplace(a_cell.Value, "(fd-\d{4}\b)", "=hyperlink(" & Chr(34) & "http://cnn.com/$1" & Chr(34) & ")")
        a_cell.Value = replaced
        Debug.Print "new value " & a_cell.Value
    Next
End Sub

Function RegexReplace(search_string, ptrn, rplc)
  Dim regEx

  Set regEx = CreateObject("VBScript.RegExp")
  regEx.Pattern = ptrn
  regEx.IgnoreCase = True
  regEx.Global = True

  RegexReplace = regEx.replace(search_string, rplc)
End Function