Excel: how to remove multiple file paths from text

Question

I have 2000s of lines in a spread sheet to reference an item image andI need to strip out the file path, but some fields have multiple image references in one cell, so for a complex example:

/2/0/2099-1.jpg,/2/0/2099-2.jpg,/2/0/2099-3.jpg,/m/a/male_headless_mannequin_-_arms_folded.jpg

need to look like this:

2099-1.jpg,2099-2.jpg,2099-3.jpg,male_headless_mannequin_-_arms_folded.jpg

but will have only one image reff, eg:

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

The formula:

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

Works great when there is only one jpg, but if there is multiple, i'm stuck.

Thanks

Answer 1

With data in A1, try:

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

If you run Excel 2019, you'd need to CSE this formula. If one has Excel O365, you don't need to CSE, but there is also a shorter version using LET():

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

---

For those interested, a link to the Q&A mentioned in my comment below.

Answer 2

Give this small User Defined Function a try:

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

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=NoPath(A1)

To learn more about macros in general, see:

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

and

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

and for specifics on UDFs, see:

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

Macros must be enabled for this to work!