Excel宏在单元格中查找文本并在单元格上插入超链接

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-####

我们正在管理Web系统中的一些系统错误，并在电子表格中为执行者设置优先级。每个票据都有一个“FD-”和四个数字作为ID。 Web系统有一个超链接，在链接的末尾有“FD - ####”。最终结果如下所示 - http://www.mytickets.com/FD-####

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

我想运行一个查找所有FD的宏 - ####并在每个上插入一个超链接。

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

单个单元格中可能有多个FD - ####，其中肯定会有其他文本。

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

我会通过每个并添加链接，但有超过150左右。

Thanks!

谢谢！

1 个解决方案

#1

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

正如评论中所提到的，Excel似乎不支持单元格中的多个超链接。

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

#1