In this tutorial, we will guide you on how to ** separate website names** or URLs from text using VBA ** in Microsoft Excel. Let’s get them below!! Get an official version of MS Excel from the following link: **https://www.microsoft.com/en-in/microsoft-365/excel
Separate the name
- Firstly, you need to create a sample data in Excel.
- In the Excel Worksheet, you have to go to the Developer Tab.
- Then, you need to ** ** select the Visual Basic option under the Code section.
- Now, you have to select the Column and go to replace option under Edit.
- After that, you need to replace http: with (spaces).
- Finally, you will find that the names get separated to a distance.
Separate web name from the text
- Firstly, in the Excel Worksheet, you have to go to Text to column option under Data.
- Then, you need to select the fixed width option.
- Now, you have to get the marker to specific location where in the column before the first website name in the whole column.
- After that, you need to click next, and you will see that the text before web names are in black.
- Finally, you have to click on the Finish button, and you will find your web names separated from the text.
To make it a Hyper link
- Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
- Then, you need to ** ** select the Visual Basic option under the Code section.
- Now, you have to go to Insert and then select Module.
- Now, you have to copy and paste the code given below.
Sub MakeHyperlinks_D()
Dim cell As Range, Rng As Range
Set Rng = Range("A1:A" & Cells.Rows.Count). _
SpecialCells(xlConstants, xlTextValues)
If Rng Is Nothing Then
MsgBox "nothing in range"
Exit Sub
End If
For Each cell In Rng
If Trim(cell.Value) <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
Address:=cell.Value, _
ScreenTip:=cell.Value, _
TextToDisplay:=cell.Value
End If
Next cell
End Sub
- After that, you need to save the code by selecting it and then close the window.
- You have to go to File and then click on Close and Exit.
- Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
- Then, you need to choose the Macros option in the Code section.
- Now, you have to make sure that your macro name is selected and click the *Run * button.
- Finally, after running the macro, you will find your Websites hyperlinked in Excel.
Wrap-Up
We hope that this tutorial gives you guidelines on how to separate website names or URLs from text using VBA in Microsoft Excel. ** ** Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!
Related Articles:
Top comments (0)