Excel: как удалить несколько путей к файлам из текста

Question

У меня есть 2000 строк в электронной таблице для ссылки на изображение элемента, и мне нужно удалить путь к файлу, но некоторые поля содержат несколько ссылок на изображения в одной ячейке, поэтому для сложного примера:

/2/0/2099-1.jpg,/2/0/2099-2.jpg,/2/0/2099-3.jpg,/м/а/мужской_манекен_без_головы_-_руки_сложенные.jpg

должно выглядеть так:

2099-1.jpg,2099-2.jpg,2099-3.jpg,мужской_манекен_без_головы_-_со_сложенными_руками.jpg

но будет иметь только одну ссылку на изображение, например:

/3/1/31-222-007.jpg,

Формула:

> =MID(AA2,FIND("*",SUBSTITUTE(AA2,"/","*",LEN(AA2)-LEN(SUBSTITUTE(AA2,"/",""))))+1,LEN(AA2))

Работает отлично, когда есть только один JPG, но если их несколько, я застреваю.

Спасибо

Answer 1

С данными в A1, попробуйте:

=CONCAT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[substring(., string-length(.) - 3) = '.jpg' or substring(., string-length(.) - 4) = '.jpg,']"))

Если вы используете Excel 2019, вам нужно будет CSE для этой формулы. Если у вас Excel O365, вам не нужно CSE, но есть также более короткая версия с использованием LET():

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s"),CONCAT(IF(ISNUMBER(FIND(".jpg",X)),X,"")))

---

Для тех, кому интересно,связьна вопросы и ответы, упомянутые в моем комментарии ниже.

Answer 2

Попробуйте эту небольшую пользовательскую функцию:

Option Explicit

Public Function NoPath(sIn As String) As String
    Dim arr, i As Long, v As String, L As Long
    Dim j As Long
    
    arr = Split(sIn, ",")
    For i = LBound(arr) To UBound(arr)
        v = arr(i)
        L = Len(v)
        For j = L To 1 Step -1
            If Mid(v, j, 1) = "/" Then
                arr(i) = Mid(v, j + 1)
                Exit For
            End If
            Next j
        Next i
        NoPath = Join(arr, ",")
        
End Function

Пользовательские функции (UDF) очень просты в использовании.установитьи используйте:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте текст и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel позже 2003, вы должны сохранить файл как .xlsm, а не .xlsx

КудалятьУДФ:

1. откройте окно VBE, как указано выше
2. очистить код
3. закрыть окно VBE

КиспользоватьUDF из Excel:

=NoPath(A1)

Чтобы узнать больше о макросах в целом, см.:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

и

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а для получения подробной информации о UDF см.:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Для работы этого метода должны быть включены макросы!