Excel提取淘宝/天猫产品的ID 货号 价格…

1.提取表格中添加在文字里的链接（*只要是文字中内嵌的链接，都可以用这个代码把链接提取出来）

Sub 提取链接()
    Selection.Cells.Offset(0, 1).EntireColumn.Insert
    For Each cell In Selection.Cells
    cell.Offset(0, 1) = cell.Hyperlinks(1).Address
Next
End Sub

2.提取表格中添加在文字里的链接，并提取链接中的id（淘宝天猫）

Sub 提取文字中链接的id()
    Selection.Cells.Offset(0, 1).EntireColumn.Insert
    For Each cell In Selection.Cells
    cell.Offset(0, 1) = cell.Hyperlinks(1).Address
    cell.Offset(0, 1) = Mid(cell.Offset(0, 1), InStr(cell.Offset(0, 1), "id=") + 3, 12)
    With cell.Offset(0, 1)
        .NumberFormatLocal = "0_ "
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    cell.Offset(0, 1).Value = "'" & cell.Offset(0, 1).Value
Next
End Sub

3.让产品id再转成链接（淘宝天猫）

Sub id转链接()
    Selection.Cells.Offset(0, 1).EntireColumn.Insert
    Selection.Cells.Offset(0, 1).EntireColumn.Insert
    For Each cell In Selection.Cells
    cell.Offset(0, 1) = "https://detail.tmall.com/item.htm?id=" & cell  
    cell.Offset(0, 2) = "//h5.m.taobao.com/awp/core/detail.htm?id=" & cell
Next
End Sub

4.提取原链接中的id，然后生成PC短链接&手机端链接（淘宝天猫）

Sub 提取id并转链接()
    Selection.Cells.Offset(0, 1).EntireColumn.Insert
    For Each cell In Selection.Cells
    cell.Offset(0, 1) = Mid(cell, InStr(cell, "id=") + 3, 12)
    With cell.Offset(0, 1)
        .NumberFormatLocal = "0_ "
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    cell.Offset(0, 1).Value = "'" & cell.Offset(0, 1).Value
    cell.Offset(0, 2) = "https://detail.tmall.com/item.htm?id=" & cell.Offset(0, 1)
    cell.Offset(0, 3) = "//h5.m.taobao.com/awp/core/detail.htm?id=" & cell.Offset(0, 2)
Next
End Sub