如何将CSV中的图片链接,变为文件名.jpg

kaijie117

表格的图片列中.含有多个图片链接,想把仅保留文件名.文件后缀
拿某个单元格为例:htt[防论坛转化为图片]ps://cdn.shopify.com/s/files/1/0555/5722/6653/files/1210445-403.8105.jpg,h[防论坛转化为图片]ttps://cdn.shopify.com/s/files/1/0555/5722/6653/files/1210445-403.8092.jpg,ht[防论坛转化为图片]tps://cdn.shopify.com/s/files/1/0555/5722/6653/files/1210445-403.8109.jpg,htt[防论坛转化为图片]ps://cdn.shopify.com/s/files/1/0555/5722/6653/files/1210445-403.8118.jpg,ht[防论坛转化为图片]tps://cdn.shopify.com/s/files/1/0555/5722/6653/files/1210445-403.jpg

转化为
1210445-403.8105.jpg,1210445-403.8092.jpg,.....1210445-403.jpg

lisongmei

G3 换成你的单元格，向下填充
=XLOOKUP("*",TEXTSPLIT(G13,"\"),TEXTSPLIT(G13,"\"),,2,-1)

kaijie117

lisongmei 发表于 2024-11-28 16:44
G3 换成你的单元格，向下填充
=XLOOKUP("*",TEXTSPLIT(G13,"\"),TEXTSPLIT(G13,"\"),,2,-1)

哥,不行

rootbot007

仅供参考

[Visual Basic] 纯文本查看 复制代码

Sub ExtractFileNames()
    Dim cell As Range
    Dim link As String
    Dim links() As String
    Dim result As String
    Dim fileName As String
    Dim i As Integer
    
    ' 假设图片链接在 A 列，从 A2 开始
    For Each cell In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        link = cell.Value
        ' 使用逗号分割链接
        links = Split(link, ",")
        result = ""
        
        ' 遍历每个链接，提取文件名
        For i = 0 To UBound(links)
            ' 提取文件名部分
            fileName = Mid(links(i), InStrRev(links(i), "/") + 1)
            ' 拼接文件名到结果字符串中
            If result = "" Then
                result = fileName
            Else
                result = result & "," & fileName
            End If
        Next i
        
        ' 将结果写入 B 列
        cell.Offset(0, 1).Value = result
    Next cell
End Sub

szh12123

Alt+F11，选当前工作表，粘贴下面代码，修改相应参数，点运行
Sub fenge()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称
    Dim a As Range
    Dim b As Range
    Dim cell As Range
    Dim arr() As String
    Dim strs As String
    Set a = ws.Range("A1:A100")  ' 括号内修改为网址的单元格范围
    Set b = ws.Range("B1:B100")  ' 括号内修改为获取名称的单元格范围
    For Each cell In a
        arr = Split(cell.Value, "/")
        Dim i As Integer
        For i = LBound(arr) To UBound(arr)
            strs = arr(i)
            Next i
        b.Cells(cell.Row, 1).Value = strs
        Next cell
End Sub

lisongmei

kaijie117 发表于 2024-11-28 17:43
哥,不行

你把 "\" 换成 "/" 啊。我电脑是\，你电脑是/  。。。