Extract web address

[Guest]

I have a list of web sites in excel, copied from another program, they are links because you can click on them and go to the web page, they look like this Ask Mr. Excel - Tips and Solutions for Excel
, is there anyway to extract the web address from it, in this case http://www.mrexcel.com/index.html

 

[Jack in the UK]

Hi--

I have tried to extract this data dfrom ***.eml files and fell flat on my face as outlook extraction into Excel... However

I have had the nasty task of this:

I copied and paste then text to cloumns, via wizard and then filtered to http:

Bingo worked for me |||||

HTH Some thoughts

Rdgs
=========
Jack

 

[Guest]

How do you do this? via wizard and then filtered to http:

 

[Russell Hauf]

Try this user-defined function. You can paste it into a module in your workbook. If you don't know how to do this, let me know.
After you have pasted it into a module in your workbook, just call it like this:

=ShowAddress(A1)

Where A1 is the cell with the MR. EXCEL hyperlink in it.

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<hr align=left width=500><font color='#000080'>Public</font> <font color='#000080'>Function</font> ShowAddress(rng <font color='#000080'>As</font> Range) <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>If</font> rng.Cells.Count > 1 <font color='#000080'>Then</font>
ShowAddress = CVErr(xlErrValue)
<font color='#000080'>Else</font>
ShowAddress = rng.Hyperlinks.Item(1).Address
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>Function</font>

</font></pre>

Hope this helps,

Russell

 

[Guest]

Thanks Russell that works Great!

 

[lopezbf]

Try this user-defined function. You can paste it into a module in your workbook. If you don't know how to do this, let me know.
After you have pasted it into a module in your workbook, just call it like this:

=ShowAddress(A1)

Where A1 is the cell with the MR. EXCEL hyperlink in it.

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<hr align=left width=500><font color='#000080'>Public</font> <font color='#000080'>Function</font> ShowAddress(rng <font color='#000080'>As</font> Range) <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>If</font> rng.Cells.Count > 1 <font color='#000080'>Then</font>
ShowAddress = CVErr(xlErrValue)
<font color='#000080'>Else</font>
ShowAddress = rng.Hyperlinks.Item(1).Address
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>Function</font>

</font></pre>

Hope this helps,

Russell

This was great. Thanks***BFL

 

[fgrouffal]

Thanks a lot! It's simple to install and works well! was looking for this for a long time!

 

[chris_redshaw]

Thank you so much Russell!

For those looking how to create a custom function, check http://office.microsoft.com/en-us/excel-help/create-custom-functions-in-excel-2007-HA010218996.aspx

Short instructions are:

Press Alt+F11 to open the Visual Basic Editor, and then click Insert, Module.

In the new module, type the code from Russell above

Then save

 

[scotharkins]

I am running Excel 2007, running a macro-enabled workbook. Whether I paste the function direct from the site, or via a paste-to-notepad-then-copy-to-VB, or when typed by hand, I get the following result when I call the function: "#NAME?" The function indicated it takes no arguments, despite the function appearing to be correctly cast (no added spaces, using different name in case of conflict, etc).

The MSKB articles come close but do not hit the problem. I am at a bit of a loss for my next step, short of running to my O'Reilly library and taking a crash course on VBS, which would probably be good in any event.

Thanks!
sh