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:


need to look like this:


but will have only one image reff, eg:


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.


  • 1
    Are you open to using a VBA function called from the sheet? If not, what about multiple helper columns? Also, your examples have the single image reference ending with a comma but the multiple references do not. Is that consistent throughout the data? Dec 1, 2020 at 13:39
  • @EngineerToast i'm open to whatever works ;) I just need to export out a new csv to helper columns are no problem. Yes the single images end with a comma, but I don't think it will be an issue for what the csv is being used for, but it also no problem to have the trailing comma removed (probably better)
    – PaddyD
    Dec 1, 2020 at 14:41

2 Answers 2


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():


enter image description here

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

  • 1
    Yet another sample for my collection. Dec 1, 2020 at 15:34
  • Thanks @Gary'sStudent. We need to also thank you for your valuable contributions all the time =). Particularly in this case since my solution would require Excel 2019 or O365.
    – JvdV
    Dec 1, 2020 at 15:39
  • 1
    The one great thing about VBA is that it will work on any version of Excel (except maybe Excel4) Dec 1, 2020 at 15:53
  • @Gary'sStudent, I'm pretty sure it can be done relatively simple through PowerQuery too, but haven't looked into that.
    – JvdV
    Dec 1, 2020 at 16:03
  • 1
    1) I need to learn XPath. 2) Can this be generalized for to work for any file extension? Dec 1, 2020 at 18:00

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

enter image description here

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:


To learn more about macros in general, see:




and for specifics on UDFs, see:


Macros must be enabled for this to work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.