Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Display the complete URL instead of a link
Output from one of my software is in excel format. The results are
something like Name Title ID source score ABC rock-n-roll 123 link 92 ABC2 rock-n-roll2 124 link 65 ABC3 rock-n-roll3 125 link 45 The "link" in the source column has the actual URL to that album or file. For further processing I need to import this file as a csv and I need the actual URL displayed in the source instead of the "link" word. For example the URL is www.somewebsite.com/documentID=AA1234 some basically I want something like ABC,rock-n-roll,123,www.somewebsite.com/documentID=AA1234,92 ABC2,rock-n-roll2,124,www.somewebsite.com/documentID=AA1222,65 ABC3,rock-n-roll3,125,www.somewebsite.com/documentID=AA1256,45 Is there a way I can do this? Thanks a lot. |
#2
|
|||
|
|||
Display the complete URL instead of a link
How about a little work against a copy of your worksheet?
You can use a UserDefined Function to extract that URL. 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 Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =getURL(a1) So insert a new column to the right of the Link column. Put =geturl(d1) in e1 and drag down Select column E and edit|copy followed by edit|paste special|values delete column D If you don't like the http:// stuff in column D (after you deleted the original column D), you can just select it and edit|Replace what: http:// with: (leave blank) replace all And then save your file as .csv. Susan wrote: Output from one of my software is in excel format. The results are something like Name Title ID source score ABC rock-n-roll 123 link 92 ABC2 rock-n-roll2 124 link 65 ABC3 rock-n-roll3 125 link 45 The "link" in the source column has the actual URL to that album or file. For further processing I need to import this file as a csv and I need the actual URL displayed in the source instead of the "link" word. For example the URL is www.somewebsite.com/documentID=AA1234 some basically I want something like ABC,rock-n-roll,123,www.somewebsite.com/documentID=AA1234,92 ABC2,rock-n-roll2,124,www.somewebsite.com/documentID=AA1222,65 ABC3,rock-n-roll3,125,www.somewebsite.com/documentID=AA1256,45 Is there a way I can do this? Thanks a lot. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display the complete URL instead of a link
Thanks Dave, this was MOST helpful!!!!
"Dave Peterson" wrote: How about a little work against a copy of your worksheet? You can use a UserDefined Function to extract that URL. 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 Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =getURL(a1) So insert a new column to the right of the Link column. Put =geturl(d1) in e1 and drag down Select column E and edit|copy followed by edit|paste special|values delete column D If you don't like the http:// stuff in column D (after you deleted the original column D), you can just select it and edit|Replace what: http:// with: (leave blank) replace all And then save your file as .csv. Susan wrote: Output from one of my software is in excel format. The results are something like Name Title ID source score ABC rock-n-roll 123 link 92 ABC2 rock-n-roll2 124 link 65 ABC3 rock-n-roll3 125 link 45 The "link" in the source column has the actual URL to that album or file. For further processing I need to import this file as a csv and I need the actual URL displayed in the source instead of the "link" word. For example the URL is www.somewebsite.com/documentID=AA1234 some basically I want something like ABC,rock-n-roll,123,www.somewebsite.com/documentID=AA1234,92 ABC2,rock-n-roll2,124,www.somewebsite.com/documentID=AA1222,65 ABC3,rock-n-roll3,125,www.somewebsite.com/documentID=AA1256,45 Is there a way I can do this? Thanks a lot. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing automatic link | Excel Discussion (Misc queries) | |||
Paste Link - retaining formatting | Excel Discussion (Misc queries) | |||
Link Link Link | Excel Worksheet Functions | |||
Breaking the link | New Users to Excel | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |