Extracting a URL from hyperlinked text in Excel cell
Solution 1
You could use a vba macro:
Hit Alt+F11 to open the VBA editor and paste in the following:
Function URL(rg As Range) As String
Dim Hyper As Hyperlink
Set Hyper = rg.Hyperlinks.Item(1)
URL = Hyper.Address
End Function
And then you can use it in your Worksheet, like this:
=URL(B4)
Solution 2
In your code just add
string myString = ((Excel.Range)xlws.Cells[2, 1]).Cells.Hyperlinks[1].Address;
I obviously recommend doing some checks before accessing the "Hyperlinks" property.
Solution 3
VBA function:
- Hit Alt+F11 (Opens Visual Basic Editor)
- Click on Insert -> Module (adds a module to your excel file)
- Paste the code below for the function of GETURL
- Hit Alt+Q (Closes the Visual Basic Editor)
Now use the =GETURL(cell)
to get the URL
Example: =GETURL(A1)
will return the URL for the Hyperlink displayed in cell A1
Function GETURL(HyperlinkCell As Range)
GETURL = HyperlinkCell.Hyperlinks(1).Address
End Function
Solution 4
Use Visual Studio Tools for Office (VSTO) to open Excel workbook and extract all hyperlinks.
I put a hyperlink into A1 of Sheet1 in Book1.xlsx: text = "example.com, address = "http://www.example.com"
_Application app = null;
try
{
app = new Application();
string path = @"c:\temp\Book1.xlsx";
var workbook = app.Workbooks.Open(path, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var sheets = workbook.Worksheets;
var sheet = (Worksheet)sheets.get_Item("Sheet1");
var range = sheet.get_Range("A1", "A1");
var hyperlinks = range.Cells.Hyperlinks.OfType<Hyperlink>();
foreach (var h in hyperlinks)
{
Console.WriteLine("text: {0}, address: {1}", h.TextToDisplay, h.Address);
}
}
finally
{
if (app != null)
app.Quit();
}
Output:
text: example.com, address: http://www.example.com/
Related videos on Youtube
AJ_
Updated on October 10, 2020Comments
-
AJ_ over 3 years
I have a table full of Hyperlinked text in excel, so it's basically a bunch of names but when I click on one, it takes me to some URL in my default browser.
So I am extracting text from this excel table in my program, but the value I get when I extract from these hyperlink cells is that of the string inside, when I want the URL the string is linked to in the excel file.
So I'm thinking there are two ways to do this. Either I can convert all the hyperlinked text in the excel file to the corresponding URLs, or I can use C# to somehow extract the URL value from the cell and not the text.
I don't know how to do either of these things, but any help would be greatly appreciated.
C# code so far:
Excel.ApplicationClass excelApp = new Excel.ApplicationClass(); //excelApp.Visible = true; Excel.Workbook excelWorkbook = excelApp.Workbooks.Open("C:\\Users\\use\\Desktop\\list.xls", 0, false, 5, "", "",false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Sheets excelSheets = excelWorkbook.Worksheets; string currentSheet = "Sheet1"; Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet); string myString = ((Excel.Range)xlws.Cells[2, 1]).Value.ToString();
As for the excel file, it's just one long row of names hyperlinked. For instance cell A2 would contain the text:
And I want to extract the string:
http://allrecipes.com//Recipes/desserts/cookies/Main.aspx
-
AJ_ about 13 yearsThanks a lot, I just used this to extract all the links to a different part of the table using my program, and then I copy/pasted them to a new excel table. Messy, but the end result is exactly what I wanted.
-
AJ_ about 13 yearsI appreciate the answer but the syntax was not working on my system and I got the job done more on the excel side of things using my C# program just a little.
-
abatishchev about 13 years@AJ: What version of Excel and VSTO do you use? I used the latest, .NET 4.0/VS 2010/Excel 14
-
AJ_ about 13 yearsI'm using excel 2003. The program is actually all done now, and is generating a database. But since there are 2700 entries, it might take around 7 to 10 hours to finish >_>
-
HaveSpacesuit over 7 yearsThis is the best answer from a coding perspective. It does not require modifying the excel worksheet. I found it necessary to change xlws.Cells to xlws.Sheets[1].Cells.
-
Timothy Gonzalez almost 7 yearsHow do you get the string URL from the hyperlinked column?