Removing text

[noregretss]

Hi,

I'm trying to remove the code &cID=(random_number_here) from a sitemap in excel. The random number is an ID when a customer views a product page. I need &cID=(random_number_here) remove from my product pages where it also comes up on the category which i would like it to stay there.

I'm looking for a macro that removes &cID=(random_number_here) from the product page and leaves it in the category page. Here is an example on what it looks like

http://www.domain.com/categories.asp?cID=430
http://www.domain.com/product.asp?pID=1964&cID=8

The link is in the same column where i want
http://www.domain.com/product.asp?pID=1964&cID=8
to be change to
http://www.domain.com/product.asp?pID=1964

[DonkeyOte]

A sample file would be helpful in this instance, however, you could try something like the below:

Option Explicit
Public Sub StripURL()
Dim strURLVar As String, strTempURL As String
Dim rngURL As Range, rngCell As Range
Dim vURL As Variant
Dim bi As Byte
Set rngURL = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
If Application.CountA(rngURL) Then
    strURLVar = Trim(Application.InputBox("Enter Variable to Strip - eg. cid", Type:=2))
    If strURLVar <> "False" Then
        strURLVar = strURLVar & IIf(Right(strURLVar, 1) <> "=", "=", "")
        For Each rngCell In rngURL
            If rngCell <> "" Then
                vURL = Split(Split(rngCell.Value, "?")(1), "&")
                For bi = LBound(vURL) To UBound(vURL)
                    If UCase(Left$(vURL(bi), Len(strURLVar))) = UCase(strURLVar) Then
                        If bi > LBound(vURL) Then
                            strTempURL = "&" & vURL(bi)
                        Else
                            strTempURL = IIf(LBound(vURL) = UBound(vURL), "?" & vURL(bi), vURL(bi) & "&")
                        End If
                        strTempURL = Replace(rngCell.Value, strTempURL, "")
                        rngCell.Value = strTempURL
                    End If
                Next bi
            End If
        Next rngCell
    Else
        MsgBox "Invalid Criteria - Please Re-Try", vbCritical, "Routine Terminated"
    End If
    Set rngURL = Nothing
Else
    MsgBox "No URLs to Process", vbInformation, "No Data"
End If
End Sub

The above is setup to fire against Column A of the selected sheet -- it will prompt user to enter variable to be stripped (eg cid) ... and will then process the strings in the column and remove the offending variable.

I hope it helps.

(With most VBA - ensure you have a backup of your present data prior to running as you won't be able to "Undo")