Details about Microsoft Excel and how to set macros to work smartly and faster. During one of my assignments, I got a huge list of MS Excel name with hyperlinks on them. Now I need to extract URL and name separately, manually its very tough but through Excel formula and macros it’s a cake walk 😊
When only name is mentioned, and we need to create URL through that name. Make sure name is in website format without space etc.
Step 1: Open Excel sheet.
Step 2: Select row B3
Step 3: Write formula in Formula bar, copy this: =HYPERLINK(“www.” & A3 & “.com”)
- Where “A3” is the section in Name column
- “www.” and “.com” is hardcode method to create link. Change accordingly like (.net)(.org)(.in) etc.
- An ampersand ‘&’ sign is used for concatenation in Excel Formulas i.e. Adding two statements.
Step 4: Just enter. Result will be seen in that box.
Now, when we have “Name” and “URL” and we need to create name with hyperlink. With formula how to do it?
Step 1: Select “Hyperlink” box as shown in below table.
Step 2: Write this formula in Formula bar: =HYPERLINK(B3, A3)
- B3: URL link
- A3: Name on which we need to assign this URL
Step 3: You are done. Now in columns, details are as follows:
- Name: Only name without link
- URL: Complete URL
- Hyperlink: Hyperlink assign on the text you select in “Name” column
Now let’s think you have Text with hyperlink, and you need to extract URL from this text through formula (when this kind of hyperlink text are in hundreds).
For this task we need to create a MACRO.
Step 1: First, go to the “Developer” menu and after that click on “Visual Basic” tab. This tab open the Visual Basic editor.
Step 2: Write below text in Visual Editor:
Function HLink(rng As Range) As String
‘extract URL from hyperlink – posted by Rick Rothstein
If rng(1).Hyperlinks.Count Then
HLink = rng.Hyperlinks(1).Address
- Check code is written in both windows i.e. “Sheet1” and “Module1”
Step 3: Now come back again in Microsoft Excel, and as now you have set “HLink” as a function which now you can use in Excel to extract URL.
Step 4: Press enter after writing function “=HLink(C3) in formula bar. Now you can see extracted URL from “C” Column text. For multilevel rows, just click and drag this D3 window, that is it. You can save lots of time as compare to manual process.
Step 5: Now save this Excel file as a “Excel Macro-Enabled Workbook(*.xlsm) through “Save As” option. This is very important process otherwise MACROS will not work.
Hope I this is helpful. In any confusion please let me know with your comments so I will try to sort those issues.
#excelsheets #macros #microsoft #extracturl #timemanagement