Solved

Excel Field with URL

Posted on 2016-07-23
21
45 Views
1 Endorsement
Last Modified: 2016-08-22
I Inherited a XLS file with a column that is a field of values that are hyperlinked, is there a way for me to create another column and extract the actual URL from the hyperlink as a text field?  THe column is C
1
Comment
Question by:Matt Pinkston
  • 10
  • 6
  • 5
21 Comments
 

Author Comment

by:Matt Pinkston
ID: 41726092
tried this but got an error

=MID(FORMULATEXT(C2);FIND(CHAR(34);FORMULATEXT(C2))+1;FIND(CHAR(34);FORMULATEXT(C2);FIND(CHAR(34);FORMULATEXT(C2))+1)-1-FIND(CHAR(34);FORMULATEXT(C2)))
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41726181
You may use UDF to get the url from the hyperlink formula...
Please follow these steps...

1) Open your workbook.

2) Press Alt+F11 to open the VB Editor

3) On VB Editor --> Insertr --> Module and paste the function given below into the opened code window.

4) Close VB Editor and save your workbook as macro enabled workbook.

Function:
Function GetURL(HyperlinkCell As Range)
  Dim arr() As String
  If Not HyperlinkCell.HasFormula Then Exit Function
  arr() = Split(HyperlinkCell.Formula, """")
  GetURL = arr(1)
End Function

Open in new window


How to use the function:
So if your hyperlink formula is in C2, try the following formula in D2 or any other cell as per requirement...

=GetURL(C2)
0
 
LVL 14

Assisted Solution

by:frankhelk
frankhelk earned 250 total points (awarded by participants)
ID: 41727111
You could use a macro function to extract the hyperlink property:

Public Function HL(c As Range)
    HL = ""
    On Error Resume Next
    HL = c.Hyperlinks(1).Address
End Function

Open in new window


This simple version lacks some smartness, but would handle most cases of cells with hyperlinks. It returns an empty string in case of an error on extracting the hyperlink, i.e. if there's no hyperlink present in the source cell.

You can use it in a cell like this:
=HL(C2)

Open in new window


See attached sample.
ExtractHyperlink.xlsm
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
ID: 41727132
Hi Frank,

As per the formula used by the Asker in the description, I assumed the hyperlinks are inserted through formulas not with inserting hyperlinks through hyperlink wizard.

But both the codes can be combined together to return the hyperlink from a cell irrespective of the method used to insert it. What do you say?

Function GetURL(HyperlinkCell As Range)
   Dim arr() As String
   GetURL = ""
   On Error Resume Next
   arr() = Split(HyperlinkCell.Formula, """")
      If UBound(arr) > 0 Then
         GetURL = arr(1)
      Else
         GetURL = HyperlinkCell.Hyperlinks(1).Address
      End If
End Function

Open in new window

ExtractHyperlink.xlsm
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41727149
Hmmm ... the term "a field of values that are hyperlinked" implies for me i.e. a list of buddies where the names are the cell content and the hyperlink points to the facebook profile ... but anyhow ...

Checking both ways would be better (I've already admitted that my first solution is not as smart as it could get). Small point: Which property to check first depends on what's expected to be more likely ... since a hyperlink property is the more distict way, I'd check that first ... but that's only the icing on top .... ;-)
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727154
Well sequence of checking won't really matter in this case. :)
0
 

Author Comment

by:Matt Pinkston
ID: 41727597
Tried Solution by SUbodah but all values end up as 0
0
 

Author Comment

by:Matt Pinkston
ID: 41727604
Sample file attached
USPSSitesx.xlsm
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727608
No sure what are you trying at your end.
Did you try the solution posted in Post ID: 41727132 and checked the file I uploaded in there?
That file contains both the solutions.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727611
Since you are not having the hyperlink formulas in the col. C, the solution posted by Frank is enough to extract the addresses in the formula cells.

Refer to the attached.
USPSSitesx.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727615
Also if you didn't inserted those hyperlinks through the formulas, why you were using FORMULATEXT function in the formula you posted in post#2?
0
 

Author Comment

by:Matt Pinkston
ID: 41727652
sorry will not let me open attached file
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727695
I think that is a temporary bug as I am also having the same issue at my end. I have reported the bug.
Please save the file first and then open it.
0
 

Author Comment

by:Matt Pinkston
ID: 41727766
nope that does not work either...

is there not just a simple formula or something manual I can do to get the urls into a separate column.  This seems way to complicated.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41727859
It's quite easy ... just follow the way Subodh pointed out in his first answer (ID: 41726181), but paste the code found in Answer ID: 41727132.

Given a hyperlinked cell in C3, enter in the desired cell (i.e. D3) the formula
=GetURL(C3)

Open in new window


Et voilá ... !

Replicate as desired.
0
 

Author Comment

by:Matt Pinkston
ID: 41731031
sorry none of the proposed methods worked
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41731035
That's strange.

Did you test the file uploaded in Post ID: 41727611?
In that file I simply copied the code from the Post ID: 41727132 and it worked perfectly when I tested it.

Then what didn't work for you? Will you please elaborate it a bit more?
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41731047
probably you could post an example Excel workbook ?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41731054
@Frank

He already posted a sample file in the Post ID: 41727604
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41732540
Ooops ... didn't walk back in the thread ... shame on me.

I've had a fresh look at that Excel file ... besides of my Excel nagging about security due to the fact that the file contains data connection, the source fields in column C are exactly what I expected: The content is text, and the cells have a hyperlink attached.

I've just replaced the code in Module1 with the code from post 41727132 and entered the formula
=GetUrl(C2)

Open in new window

into cell X2. Works like a charm.

I've attached that edited sample to this post ... (I know you've already such a sample, but maybe tze asker has more luck with this one)
USPSSitesxExample.xlsm
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41757587
All the needed work has been done by sktneer and frankhelk. The best solution chosen takes care of any type of hyperlink inserted into a cell.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now