» Seperating Website Name or URL from Text

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.

Step 1.

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

Step 2

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

FINISH

You will find your web names seperated from the text.

Now to make it a Hyper link:

Step 1

Press Alt + F11

you will find that a Macros will open

Goto Insert and then select Module

another New Module Window pops up

Step 2

Copy paste the below mentioned formula:

------------------------------------------------------

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

-----------------------------------------------------

and then Goto File Close and Exit

Step 4

Goto Tools>Macros>Macros

you will find your Websites hyperlinked.


SIMPLE AND RAW BUT EFFECTIVE.

Jay.