Clean up datas with VBA and regex

[Mercury74]

Hi,

I want to clean a worksheet with multiple lists of urls. Those urls have some strings at the beginning and at the end.
For exemple:

sdab588dddhttp://www.url1.com/page.php&sa=qsd554/ds4d
dab588dddhttp://www.url2.com/index.html&sa=fffsdf?result=4
dab588dddhttps://www.urlx.com/&sa=dfdf/5454?fdfd&id=24
...

Black = url to keep
Red = strings to remove


Important :

• Every strings to remove after urls always start with &sa=
• Some urls start with https, so take care!
• I want a "click and cleanup" solution with VBA, not a formula
• The number of columns is variable
• Do not create any new columns with cleaned urls, but simply erase unwanted strings.

Thanks!

 

[dcardno]

I don't use regex, but this will work, none-the-less:

Sub CleanURLs()

Dim cl      As Range
Dim stVal   As String
Dim iLeft   As Long
Dim iRight  As Long


For Each cl In Selection

iLeft = InStr(cl.Value, "http")
iRight = InStr(cl.Value, "&sa")

stVal = Mid(cl.Value, iLeft, iRight - iLeft)

cl.Value = stVal

Next cl

End Sub

Select the range to clean, and then run the macro.

 

[Rick Rothstein]

Here is another macro that should work for you...

Sub CleanURLs()
  Cells.Replace "&sa=*", "", xlPart, , False
  Cells.Replace "*http", "http", xlPart, , False
End Sub

 

[snb_]

Sub M_snb()
    Cells.Replace "*http", "http"
    Cells.Replace "&*", ""
End Sub

 

[Rick Rothstein]

Sub M_snb()
    Cells.Replace "*http", "http"
    Cells.Replace "&*", ""
End Sub

I would suggest that, at minimum, you add the third argument xlPart (see my code in Message #3), otherwise your code will fail to work if the last time Find or Replace was used (either manually or though other VB code), the "Match entire cell contents" option box was checked or specified.

 

[snb_]

In that case you only need it once:

Sub M_snb()
    Columns(1).Replace "*http", "http", 2
    Columns(1).Replace "&*", ""
End Sub

 

[Mercury74]

Thanks!