The tip submitted by: Jay Shukla
Some times we have a situation like:
1. Yahoo website www.yahoo.com
2. Google Website www.google.com
So How do we seperate the website name form the remaining text.
Select the Column.
Goto Replace option under Edit
Then replace http:
with (spaces) …………………….http:
(Eg.: …. = Spaces)
You will find that the we names get seperated to a distance
Goto Text to column option under Data
and select Fixed width option
get the marker to specific location where in the column before the first website name in the whole column
click next u will see that the text before web names are in black
You will find your web names seperated from the text.
Now to make it a Hyper link:
Press Alt + F11
you will find that a Macros will open
Goto Insert and then select Module
another New Module Window pops up
Copy paste the below mentioned formula:
Dim cell As Range, Rng As Range
Set Rng = Range(“A1:A” & Cells.Rows.Count). _
If Rng Is Nothing Then
MsgBox “nothing in range”
For Each cell In Rng
If Trim(cell.Value) <> “” Then
ActiveSheet.Hyperlinks.Add Anchor:=cell, _
and then Goto File Close and Exit
you will find your Websites hyperlinked.
SIMPLE AND RAW BUT EFFECTIVE.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.