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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube