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

  • 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? – Engineer Toast Dec 1 '20 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 '20 at 14:41
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.

  • 1
    Yet another sample for my collection. – Gary's Student Dec 1 '20 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 '20 at 15:39
  • 1
    The one great thing about VBA is that it will work on any version of Excel (except maybe Excel4) – Gary's Student Dec 1 '20 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 '20 at 16:03
  • 1
    1) I need to learn XPath. 2) Can this be generalized for to work for any file extension? – Engineer Toast Dec 1 '20 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!

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.