Matt Pinkston
asked on
Excel Field with URL
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
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:
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)
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
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 .... ;-)
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 .... ;-)
Well sequence of checking won't really matter in this case. :)
ASKER
Tried Solution by SUbodah but all values end up as 0
ASKER
Sample file attached
USPSSitesx.xlsm
USPSSitesx.xlsm
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.
Did you try the solution posted in Post ID: 41727132 and checked the file I uploaded in there?
That file contains both the solutions.
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
Refer to the attached.
USPSSitesx.xlsm
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?
ASKER
sorry will not let me open attached file
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.
Please save the file first and then open it.
ASKER
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.
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.
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
Et voilá ... !
Replicate as desired.
Given a hyperlinked cell in C3, enter in the desired cell (i.e. D3) the formula
=GetURL(C3)
Et voilá ... !
Replicate as desired.
ASKER
sorry none of the proposed methods worked
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?
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?
probably you could post an example Excel workbook ?
@Frank
He already posted a sample file in the Post ID: 41727604
He already posted a sample file in the Post ID: 41727604
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
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
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)
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
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.
ASKER
=MID(FORMULATEXT(C2);FIND(