5

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

2
  • 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

6

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,"")))

enter image description here


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

6
  • 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
4

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:

=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!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .