|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» 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. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Mastering Excel 2000 (for beginner)
Financial Modeling - 2nd Edition
Word 2002: The Complete Reference
Special Edition Using Microsoft Excel 2002
Finance and Accounting for Nonfinancial Managers
The Intelligent Investor: The Classic Bestseller on Value Investing | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|