Extracting Urls into another column.

Hi, as a web developer I often have a list of Links that i need to work
on. 
When I copy from a webpage and paste into Excel I get the Linktexts
(ie. _MY_WEBSITE_ ) and would like to have the Url (Webadress) 'behind'
this text in another column (ie. www.mywebsite.com).
The macro I have managed only copies the Linktext (with the recording
function)

Thanks for any help!


---
Message posted from http://www.ExcelForum.com/

0
12/20/2003 3:07:34 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
358 Views

Similar Articles

[PageSpeed] 7

You could write your own (a userdefined function).

I used ctrl-K (insert|Hyperlink) to create the link:

Option Explicit
Function GetURL(Rng As Range) As String
    Application.Volatile

    Set Rng = Rng(1)
    
    If Rng.Hyperlinks.Count = 0 Then
        GetURL = ""
    Else
        GetURL = Rng.Hyperlinks(1).Address
    End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

rafter wrote:
> 
> Hi, as a web developer I often have a list of Links that i need to work
> on.
> When I copy from a webpage and paste into Excel I get the Linktexts
> (ie. _MY_WEBSITE_ ) and would like to have the Url (Webadress) 'behind'
> this text in another column (ie. www.mywebsite.com).
> The macro I have managed only copies the Linktext (with the recording
> function)
> 
> Thanks for any help!
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/20/2003 3:55:16 PM
Reply:

Similar Artilces:

unable to display #####.ost
Have problems using Outlook 2003. Periodically I get "unable to display the folder. The file ####.ost could not be accessed." Just recently I've got unable to display the folder. "The file ###.ost could not be accessed because another workstation has accessed it. close and restat any web-enabled applications." What could be causing this? the ost is being kept on a NT 4 file server running server protect. my local PC is running w2k and trend antivirus. Sukh You shouldn't be storing your OST on a network share. MS recommends against it since other processes could...

use XmlDataDocument load from an URL with username password
I have a piece of code to read a xml file from the web and put it into a variable in SSIS, but i don't know how to get it if it's username and password protected: URL = "http://www.mysite.com/setup.xml" xmlDoc = New XmlDataDocument xmlDoc.Load(URL) Anyone can help me? you need to specify an XmlResovler and add the necessary credentials to it. XmlUrlResolver oXmlUrlResolver = new XmlUrlResolver(); oXmlUrlResolver.Credentials = CredentialCache.DefaultCredentials; URL = "http://www.mysite.com/setup.xml" xmlDoc = New XmlDataDocument xmlDoc....

In newsgroup postings, why do I see so many URLs with broken hyperlinks?
It seems to be one of those little know "secrets" . . . Why is it that I find so many newsgroup postings with non-working hyperlinks? - You know the one's - they are long enough to line-wrap only the hyperlink doesn't "wrap" with them - so to speak. But then I see other URLs that are very long and might even wrap more than one line yet they work just fine. Who can tell me why? And more importantly, who can tell me what I must do to ensure that my own hyperlinks are never broken in this manner? Thanks all, - Stan Shankman Depends on your news reader, and the e...

Column Charts for Pie Slices?
Hi. What a great forum! Hope someone can help me (went back about a month and didn't see this question). My pie chart has 5 slices (# of employees for each of 5 departments). Each slice consists of 2 values (male, female). I'd like each slice to "point" to its own stacked column chart that shows how the slice is distributed across the two values. So the pie chart itself shows the distribution of employees across depatments, and the 5 associated stacked column charts show the relative number of male and female employees for each dept. Any ideas? Thanks! You could ma...

adding 2 columns of currency
=D5-SUM(D6:D92,H6:H92) i thought worked but for some reason i can't get it to. Can anyone help me ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Copy and Paste from one Publisher doc to another?
I have created a group of objects in Publisher 2002. I need to use this group of objects in another Publisher document. The program won't let me copy and paste from one to another. I can do this in other programs, why not Publisher? How do I get these items into another Publisher document?? Open the Office Clipboard in the Edit menu. Does that help? I just tried 2002, when I had a text box selected in the second publication I could not paste. I deleted the text box and the Office clipboard behaved. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news:...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Export/Import Acces Table to another Acces table/file
Hi, Is anybody can help me how to export/import table to a new table/file only using 1 click?. I am sure, we'll use Visual Basic, but I don't know how. Thanks in advance, I am appreciated it. KT -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 I suspect you will need to look at the TransferDatabase method of the DoCmd object. See the Access help. Steve "KimTong via AccessMonster.com" wrote: > Hi, > > Is anybody can help me how to export/import table to a new table/file only > using 1 ...

Stacked Column Graph
I have data that counts in two separrate ways. How can I put two stacked columns next to each other on the same graph (X is in days, Y1 is # of incidents, Y2 is currently an average of days used for a line above the stacks). Hi Tim, Have a look at the resources referred to by Jon. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy Tim wrote: > I have data that counts in two separrate ways. How can I put two stacked > columns next to each other on the same graph (X is in days, Y1 is # of > incidents, Y2 is currently an average of days used for a line a...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

exchannge 2003 server on another server
dear all , I have one Server running on windows 2003 server standard with active directory and another new server plan to deply exchange server , my problem is when i install exchange server 2003 , setup /forestprep it unable locate to AD , please help , thank you . Check your DNS settings. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "ys" <ys@discussions.microsoft.com> wrote in message news:0909D69D-AEE2-41FA-BD45-C96C46DB64B1@microsoft.com... > dear all , > I have one Server running on windows...

another EXPERT LEVEL FORMULA from me
Hi All A B 1 #N/A d 2 #DIV/0!, h 3 5 k 4 3 t In Sheet1 I’ve got two columns: Column A and Column B. What I’m tiring to do is to create a formula to get the value of the first cell in a column “B” which corresponding (offset) cell in column A is the first row in Column A with NO ISERROR on it like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! In the example above this is “k” (the value in cell B3). So far my guided formula is array and look like this::=INDEX(A1:A20,MIN(ROW(B1:B20)*(B1:B20<>#N/A))) but it doesn’t work at all. I’m also planning the formula to be in ...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Another question
I used Money to go to this page: http://www.microsoft.com/money/special/MoneySpecialPages_Launch2006.mspx Here is what this page states: Important things to know before you download Microsoft Money 2006 Premium: This download offer is available in the US only. You must have a Microsoft Passport to purchase and download the software. What if I don't want to use Passport? I don't have one and don't want to get one, or if I do have one, I have no idea waht it or the password. If I buy it from a store, will I have to use Passport to install and use it? I don't use...

how do I import pages from one publisher file to another
I am trying to merge two documents made with publisher into one document. I am finding this entire process frustrating What version Publisher? There is this for 2007 Publisher Tools PubCat v1.0.0.0 http://ed.mvps.org/Static.aspx?=Publisher/tools -- Mary Sauer http://msauer.mvps.org/ "AKB" <AKB@discussions.microsoft.com> wrote in message news:B41D7855-F0CF-47A3-B215-5D3B0B4D5542@microsoft.com... >I am trying to merge two documents made with publisher into one document. I > am finding this entire process frustrating A p.s. I have a convoluted way to combine 2003 d...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Could not determine which columns uniquely identify the rows
All, When I try to configure an odbcDataAdaptor through an ODBC connection to an Excel spreadsheet I the error message on the subject line. It applies to the SQL Insert and Update commands. I think I know that it is telling me that there is no primary key assigned for the table that is being referenced in the spreadsheet. What I cannot figure out is how to specify the primary key for the named range in the spreadsheet. TIA Bill ...

"Switch To" another application
I want to pop a running application up to the top of the screen. Basically I want to simulate the functionality of the "Switch to" feature in Task Manager. Naturally, it will not work. First off, I obtain the process handle by using EnumProcesses(). Next, after realizing that this was an area of the API with lots of holes, I find I must jump through hoops in order to "Switch To" this application. Next, I call GetProcessID() on my process handle. Next, I call EnumThreadWindows which inconvienently uses a callback function to obtain the window handles of the windows of t...