Macro to trim text in cell with condition

[V51773]

Hello All,

Im looking into learning how to macro all the cells say in column A to remove the following from a website entry, example http://www.PubHorn01.com.ar or https://www.PubHorn01.com.ar or www.PubHorn01.com, http://www.PubHorn01.com/adasdasd, now i've tried using find and replace then recording it as a macro, but my problem is the data is not constant, i just want my end result to be .PubHorn01.com.ar, but again that raises another issue some website just ends with .com , some website has .com.ph , au , ar etc . so the condition must be itll keep both .com.xx and .com. and it would also ignore not make any changes to correct format entries.

from this data

http://www.PubHorn01.com/asasasasas

http://PubHorn01.com/asasasasas

https://www.PubHorn01.com.ar/asasas/asasasa

www.PubHorn01.com.xx/
www.PubHorn01.tk
www.PubHorn01.info
PubHorn01.info

expected results is

PubHorn01.com
PubHorn01.com.ar
PubHorn01.com.xx
PubHorn01.tk
PubHorn01.info
PubHorn01.info

 

[V51773]

Hello Mohadin,

Please see attachment, higlighted in yellow for bug outputs.

Thank you for your help.

 

[Dan_W]

I wasn't aware that this was going to be an evolving and growing dataset

 

[Dan_W]

Hello Dan,

Thank you for your reply, for some reason the above code is giving me a #value error. also i have one more condition needed.
entry
www.john.blogspot.com

output
john.blogspot.com

Thank you,
Mike

It's giving you an error, because I wrote it on the assumption that when you said

from this data

http://www.PubHorn01.com/asasasasas
http://PubHorn01.com/asasasasas
https://www.PubHorn01.com.ar/asasas/asasasa
www.PubHorn01.com.xx/
www.PubHorn01.tk
www.PubHorn01.info
PubHorn01.info

you meant

from this data

http://www.PubHorn01.com/asasasasas
http://PubHorn01.com/asasasasas
https://www.PubHorn01.com.ar/asasas/asasasa
www.PubHorn01.com.xx/
www.PubHorn01.tk
www.PubHorn01.info
PubHorn01.info

 

[V51773]

Hello Dan,

Sorry about that, I've went on ahead and used Mohadins solution, and just added a counter argument when it fails, as it seems the constant value i can base on there are the https: and www, apologies for the hassle, as I've realized the latter requests while i was fiddling with the codes ya'll generously supplied, I was about to edit the main thread but it wouldnt allow me anymore. Thanks again for your help and sorry for the hassle.

regards,
Mike

 

[Peter_SSs]

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Here is another code you could test.

Sub WebsiteInfo()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "((https?://)?(www\.)?)"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
        a(i, 1) = Left(RX.Replace(a(i, 1), ""), InStr(1, RX.Replace(a(i, 1), "") & "/", "/") - 1)
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

My sample data and results:

V51773.xlsm
	A	B
1
2	http://www.PubHorn01.com/asasasasas	PubHorn01.com
3	http://PubHorn01.com/asasasasas	PubHorn01.com
4	http://www.PubHorn01.com/asasasasas/asasasa	PubHorn01.com
5	www.PubHorn01.com.xx.yy/aw	PubHorn01.com.xx.yy
6	PubHorn01.info	PubHorn01.info
7	john.blogspot.com	john.blogspot.com
8
9	http://z--z.com/	z--z.com
10	www.man-whole.com	man-whole.com
11	http://yongqing.is-programmer.com	yongqing.is-programmer.com
Sheet1

 

[V51773]

Hello Peter,

Thank you for your help, this is exactly what i was looking for, just one question just so i can understand how the programming works, apologies for the hassle on the sample data.

 RX.Pattern = "((https?://)?(www\.)?)"  <-this is where you remove the http and www.

my question is if say i would add, ftp or rtp, or any other name that would be invented in the future , do i add it like this?

RX.Pattern = "((https?://)(ftp?://)?(www\.)?)"

sorry for the dumb question, i really want to understand how this argument works, how did the code know it should ignore "/" sorry for the long question my understanding of this is still pretty vague. i merely code with tons of nested ifs ahhaha

also if theres a book or documentation you can point me through so i can read more on this , thank you very much.


Thank you so much for your help.

regards

Mike

 

[V51773]

Hello Peter,

Disregard the last inquiry, i played around with the code and got it to work.

  RX.Pattern = "((https?://)?(ftp\.)?(ww3\.)?(www\.)?)" <-- this is what i got

Thank you for your help, I was able to understand the gist of it all, just need to read more on regrex as this is my first time encountering it <3, thanks again and if there are books or documentation you can point me through to study that would be great,

Hello Dan,

Thank you for your help as well..


Hello Mohadin,

Thank you for your assistance too, ill compare your code with Peter and see where i can make out what their differences are.


regards

Mike

 

[Peter_SSs]

Disregard the last inquiry, i played around with the code and got it to work.

RX.Pattern = "((https?://)?(ftp\.)?(ww3\.)?(www\.)?)" <-- this is what i got

Thank you for your help, I was able to understand the gist of it all, ...

You're welcome.
.. and that pattern looks pretty good to me.

.. if there are books or documentation you can point me through to study that would be great,

Finding info for regular expressions used with vba I found somewhat difficult to find.
I did purchase this book and it was some help. It is now out of print but there is a further link on that page to a replacement book which I assume is similar.
There are also some websites if you search for them. A few rom my list, in no particular order
Excel Regex Tutorial (Regular Expressions) - Analyst Cave
Regular expressions - An introduction
Zytrax Tech Stuff - Regular Expressions - A Gentle User Guide and Tutorial (includes a testing capability)

However, I did am still doing most of my learning of regular expressions in this forum.

 

[V51773]

Hello Peter,

Thank you again for your help and the study/review material, have a wonderful day chief.

regards,
Mike