HELP with search in strings

[gtben]

Hey all,


I have URL strings such as:
url/Live-to-Ride-Long-Sleeve-T-Shirt-5223/
url/Live-To-Ride-Motorcycle-Long-Sleeve-T-Shirt-4963/
url/Live-To-Ride-Motorcycle-T-Shirt-4964/
url/Live-To-Ride-Motorcycle-Women-T-Shirt-4965/
url/Live-To-Ride-Motorcycle-Women-T-Shirt-6096/
url/Live-to-Ride-T-Shirt-5224/
url/Live-to-Ride-Women-T-Shirt-5225/


and i need to stay with the urls the has only "T-Shirt" in them
and the strings with the words "Long" or "Women" will be remove

the output to those urls needs to be:


url/Live-To-Ride-Motorcycle-T-Shirt-4964/
url/Live-to-Ride-T-Shirt-5224/

 

[Chris Macro]

Are you wanting to do this via VBA or an Excel Formula?

 

[shyy]

=IF(COUNT(SEARCH({"Long","Women"},A1)),"",A1)

 

[JoeMo]

Here's a VBA solution. Assumes your url strings are in column A starting in cell A1 and lists out the ones you want in column C starting in cell C1.

Sub CondenseURLs()
Dim lR As Long, R As Range, vA As Variant, vO() As Variant, i As Long, ct As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1:A" & lR)
vA = R.Value
ReDim vO(1 To UBound(vA, 1))
For i = LBound(vA, 1) To UBound(vA, 1)
    If InStr(vA(i, 1), "T-Shirt") > 0 Then
        If InStr(vA(i, 1), "Long") = 0 And InStr(vA(i, 1), "Women") = 0 Then
            ct = ct + 1
            vO(ct) = vA(i, 1)
        End If
    End If
Next i
ReDim Preserve vO(1 To ct)
Range("C1:C" & ct).Value = WorksheetFunction.Transpose(vO)
Columns("C").AutoFit
End Sub

 

[Chris Macro]

=IF(COUNT(SEARCH({"Long","Women"},A1)),"",A1)

Shyy - I believe you forgot the T-shirt Criteria in your formula. Maybe try:

=IF(AND(COUNT(SEARCH({"Long","Women"},A1))=0,ISERR(SEARCH("T-Shirt",A1))=FALSE),A1,"")

 

[gtben]

=IF(COUNT(SEARCH({"Long","Women"},A1)),"",A1)

thanks A million!!!!
YOUR ARE THE BEST

 

[shyy]

If you want to actually remove them, has to be done VBA.