CODEWITHSUNDEEP
excelhyperlink
Brian Kubricky
Brian Kubricky

Reputation: 63

Deleting hyperlink text from Excel text

Is there a formula/built in feature for excel where I can remove anything that starts with an "http" in a row of text? They could be anywhere in the text as well. As an example,

Say I have "John went to the park http\centeralpark.com and ate a hot dog https\hotdog.com that he liked very much"

So what I would want here is the text with no links:

"John went to the park and ate a hot dog that he liked very much"

Any easy way to use this?

Thanks in advance

Upvotes: 0

Views: 84

Answers (1)

VBA Pete
VBA Pete

Reputation: 2666

Use a VBA function to remove all instances of your hyperlink:

Formula:

=RemoveHyperlink(A1)

enter image description here

Code:

Public Function RemoveHyperlink(ByVal value1 As String) As String
    Do While InStr(value1, "http") > 0 And InStr(value1, ".com") > InStr(value1, "http")
        value1 = Left(value1, InStr(value1, "http") - 1) & Mid(value1, InStr(value1, ".com") + 5)
    Loop
    RemoveHyperlink = value1
End Function

Upvotes: 2

Related Questions