UDF: Formula()
Post date: May 14, 2013 6:59:04 PM
Create an incredibly simple, yet useful UDF (User Defined Function).
Hyper link extraction
Below is a post from a forum I frequent:
"I need help with a task. I have a column in excel, that has hyperlinks and under the hyperlink it has an e-mail address that I need to pull...in to a new column....any help would be appresiated. I try Mid, left, right formula...but no luck."
I'm assuming the poster wants to pull the e-mail address from an XL formula like this:
=HYPERLINK("Craig_Hatmaker@MyMail.com", "Craig's Fictitous email")
It seemed obvious that all the poster needed was a little string manipulation of the formula. Sounds simple. But wait - what is the XL function to display a cell's formula? I couldn't think of anything and the function wizard provided no help. After Googling for answers I concluded no such function exists in XL. But a solution could be quickly made from VBA. Here is an example:
http://en.kioskea.net/forum/affich-85224-excel-formula-display-in-cell
The following is based on their solution. I modified their solution slightly for personal preference.
NOTE! I have no error checking here. I recommend adding error handling to all routines. For more on error handling see: https://www.dropbox.com/s/98ipp2lcvr2u8vo/Error_Handling.pdf?dl=0
Public Function Formula(oCell As Range) As String
Formula = oCell.Formula
End Function
Once the above code is added to a standard VBA module, XL gains a new UDF. Here is an example use:
=Formula(A4)
Now all that does is display cell A4's formula. To complete the poster's request, we need to include our UDF in a formula to extract the link from something like:
=SUBSTITUTE(MID(Formula(A4),12, FIND(",",Formula(A4))-12),"""","")
For more on extending XL by creating UDFs see:
https://www.dropbox.com/s/u9dcgursm9wof9p/User%20Defined%20Function%20-%20Eval.pdf?dl=0