Is it possible to find and delete anything between two specified characters in an excel csv cell?-CodePudding

I have a csv file, where images links are added in one cell for one product. I want to remove text form ? to ,

I write this code:

=MID(A2,1,FIND("?",A2)-1)&MID(A2,FIND(",",A2),LEN(A2))

But its applied only on the first image link.

This is what I have:

/images/image1.jpg?1200x800=new, /images/image2.jpg?1200x800=new,/images/image3.jpg?1200x800=new, /images/image5.jpg?1200x800=new

Result I need:

/images/image1.jpg,/images/image2.jpg,/images/image3.jpg,/images/image5.jpg

CodePudding user response：

If your data is in A1:

=TEXTJOIN(",",,LET(x,TEXTSPLIT(A1,,","), y, LEFT(x,FIND("?",x)-1),y))

If you have Excel 2016 or earlier, which lack both the TEXTJOIN function as well as dynamic arrays, I suggest using a VBA routine to produce your desired output.

I used a regex match method to extract each segment, then joined them together. You could use a regex replace method, but since your original data has zero or one spaces after each comma, that would be the case in your result string also, so not as much under your control.

To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like =Images(cell_ref) in some cell.

Option Explicit
Function Images(S As String) As String
    Dim RE As Object, MC As Object, M As Object
    Dim AL As Object
    
Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = "([^\?, ]*)\?"
    .MultiLine = True
    .Global = True
    If .test(S) Then
        Set MC = .Execute(S)
        Set AL = CreateObject("System.Collections.ArrayList")
        For Each M In MC
            AL.Add M.submatches(0)
        Next M
    End If
End With

Images = Join(AL.toarray, ", ")
        
End Function