VBA: Search text and extract hyperlink in same cell

[sroysroy]

Search text "Value link" any where in each sheet except x,y and z.
And then extract hyperlink website address in same cell


Sub addtext1()
Dim ws As Worksheet For Each ws In Worksheets If ws.Name <> "x" And ws.Name <> "y" And ws.Name <> "z" Then

Your CODE

End If End If Next ws End Sub

[Jakobshavn]

What do you want done with the extracted hyperlink address?

[sroysroy]

Data formatting in excel sheet.

[Jakobshavn]

Very good!

Now if we find a hyperlink in cell B9 and extract its address, shall we put that address back in B9 or in C9 or in B10?

[sroysroy]

If we find a hyperlink in cell B9 then extract its address in B9.

[Jakobshavn]

Try this:

Please Login or Register  to view this content.

[sroysroy]

I have to put the word "Value link" in the code so that it can find it and extract the text despite anywhere it occurs in the used range of each sheet except x,y and z. See my first post.

[sroysroy]

If we find a hyperlink in cell B9 then extract its address in B9 - apologies for the misunderstanding. If "Value link" appears in B9 then extract its address in B9.

[Jakobshavn]

I have added a test for "Value link"

Please Login or Register  to view this content.

[sroysroy]

Worked beautifully. Thanks.
And what if I want to extract all hyperlinks in column C.

[Jakobshavn]

Do you want all the hyperlinks is column C extracted or only the "Value link" ones??

[sroysroy]

Yes all the hyperlinks is column C.

[Jakobshavn]

UNTESTED:

Please Login or Register  to view this content.

[sroysroy]

Worked. Thank you.

[Jakobshavn]

You are very welcome!

[sroysroy]

Finally I want to clear all other hyper linked texts or sentences if existed in used range of each sheet except x,y and z.
PS:This is not weblink extracting. Just a code which turns text or sentences into not hyperlinked. Hope I am clear.