Thread: Macro that removes cariable before set characters

Hi Excel Forum!

Hoping someone can help me with a macro? I need to remove part of a string from several rows. The data looks like so...

http://domain.com/_ydlt=AfghTW_02JMxdgfh4xOKNcIF;_uylv=0/Sghjghjtg/**http://www.website1.com/
http://domain.com/_yglt=A0WTW_03FMx4OGMQAvymKNcIF;_uylv=0/SIG=13hmt1pttm/**http://www.website2.com/
http://domain.com/_gylt=A0WTW_05MsxeOjBEBNRKKNcIF;_uylv=0/SIG=119hto9nd8/**http://www.website3.com/
http://domain.com/_hylt=A0WTW_07OMxO34R0B7wuKNcIF;_uylv=0/SIG=1h10hntfuv/**http://www.website4.com/
...

As you can see the each string is unique before the double asterisks (**). The only thing that is consistent in each row are the double asterisks.

The portion of the string that I need isolated are the URLs at the end of each string after the characters double asterisks (**) in the example above.

Here is what a sample output would look like...

http://www.website1.com/
http://www.website2.com/
http://www.website3.com/
http://www.website4.com/

So I figure I need a macro to either remove all of the characters before double asterisks? Is that correct? Can anyone provide a macro that can accomplish what I need?

Thanks!

This should do the job

Sub del_Astrisk()
Dim MyCell As Range
For Each MyCell In Range("A2:A" & Range("A" & Rows.count).end(xlup).row)
On Error Resume Next
MyCell = Mid(MyCell, Application.WorksheetFunction.Find("**", MyCell) + 2)
Next MyCell
End Sub

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

JB, it's great to give an alternative but the OP asked for a macro and posted in the excel vba programming forum

You're both awesome just for answering my post in my book!

Originally Posted by Simon Lloyd

This should do the job

Sub del_Astrisk()
Dim MyCell As Range
For Each MyCell In Range("A2:A" & Range("A" & Rows.count).end(xlup).row)
On Error Resume Next
MyCell = Mid(MyCell, Application.WorksheetFunction.Find("**", MyCell) + 2)
Next MyCell
End Sub

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

Thanks Simon! This worked perfectly!

Originally Posted by JBeaucaire

No macro needed, put this formula in B1 and copy down:

=MID(A1, FIND("http", A1, 4), LEN(A1))


Or

=MID(A1, FIND("**", A1, 4) + 2, LEN(A1))

JBeaucaire, since I love to learn, I can't seem to get it to work probably due to my lack of excel chops. When I entered the formula into B1 and copeid down "#VALUE!" was returned in all of my column C rows. There was no URLs though. Any suggestions?