vba

Extract link from text using VBA

by adminin Visual Basic for Applicationon Posted on
Rate

Use VBA to extract links from pre-embedded links

What is Link Embedded Text?

Text embedded links are also known as Hyperlinks, they are linked to a specific path on a computer (local) or a website.

You can insert links for documents using tools in the Microsoft Office suite such as Word, Excel, Powerpoint, or using tags a in web development.

So what do you need to do to extract links from these texts?

Extract links from text containing links

vba

Have you ever received an e-mail with a bunch of text containing download links for data files.

You want to separate these links for bulk download with the utility Open Multiple URLs, Tuan will guide you to extract these links using VBA with Excel.

Instruction

First, highlight the text and paste it in Excel, remember to use "Keep source formatting" when pasting.

Then, you open VBA in Excel with the combination ALT + F11, or click the Visual Basic icon in the upper left corner in the Developer tab.

In the Insert menu, select Module to add a new Module.

Then you, enter the code as below:

				
					'TODO: Get link from text
Public Function getLink(range As range)
    getLink = range.Hyperlinks(1).Address
End Function

Next, close the VBA window and return to the Excel spreadsheet.

Now, you have a new function called getLink with the following syntax, used to extract the link. 

				
					=getLink(cell)

In which, cell is the cell containing the text embedded in the link.

Tuan will enter the above formula in cell B1, with the cell containing the linked embedded text A1.

vba

So you have successfully extract the link, flash fill down the remaining lines to finish.

Above is an article on how to extract links from text in Excel using VBA.

Wish you success in applying to work or study!

Recent Posts

Leave a Reply