Specifying Default Hyperlink Text

@Dennis & @Kim

Here's the easy solution for making InStrRev available on the worksheet. All I did was to make the UDF, pass-through the arguments from the worksheet to the VBA function, assigned the result and voila! a worksheet function mimicking what was previously a VBA-only function.

Function udf_InStrRev(StringCheck As String, _
StringMatch As String, _
Optional Start As Long = -1, _
Optional Compare As VbCompareMethod) As String
udf_InStrRev = InStrRev(StringCheck, StringMatch, Start, Compare)
End Function

There is a somewhat simpler formula, too:

=HYPERLINK(A1, RIGHT(A1,LEN(A1)-FIND(CHAR(1), SUBSTITUTE(A1, "", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1,"","")) ))))

As with the original, the idea here is to figure out how many "" characters there are in the string, and then replace the last one with a non-printing character - hence CHAR(1) - and the find that character in the original string. We're still counting the "" by comparing the length of the original string against one with all the ""s removed, and using that as the "which instance to substitute" value. And then finding the sentinel.

My version of the formula is a little simpler in that instead of using another SUBSTITUTE to replace the leading characters (the result of the LEFT function) with a null string, you can simply use the RIGHT function to select all the characters other than the ones to the left (hence, the length of the original string minus the position of the sentinel).

And I agree with the earlier poster - this would be soooo much easier if we could use InStrRev in a function.

Never mind - I figured it out by looking at additional information on the net. Clever. You would think Excel would provide an easier non-macroish way by exposing the VBA InStrRev function to the spreadsheet interface. Would certainly make life a lot easier.
Cheers!

@Willy...I was trying to think of that last week and too busy to look it up. Thanks.

That will fix them after the fact, and en masse. But if we put it in the appropriate event procedure it will do it as each on is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Hyperlinks.Count > 0 Then
Application.EnableEvents = False

Target.Hyperlinks(1).TextToDisplay = _
Mid(Target.Hyperlinks(1).TextToDisplay, InStrRev(Target.Hyperlinks(1).TextToDisplay, "") + 1)
Application.EnableEvents = True
End If
End Sub

I'm confused on what the CHAR(1) is doing for the function. I copied this function into my spreadsheet and it is coming up with a #VALUE! error. An ASCII chart look up reveals a CHAR(1) is equivalent to SOH or Start of Heading. Can someone tell me how that is applied here? The last time I used SOH was about 100 years ago with paper tape and then magnetic tape @ 1600 bpi. Ah - the good ol days...

That works fine if you have only a few hyperlinks but if you have a bunch of them, a macro will be handy.

The one in tip can be drastically shortened though. First of all the While Wend loop is obsolete and maintained only for backwards compatibility. It is now recommended to use the Do ... Loop statement that provides a more structured and flexible way to perform a looping.

But most of all it isn't needed here at all. It searches for the last instance of the backslash but VBA provides a function that does simply that in a single operation: InStrRev.

My 6 line macro does the job of this tip's 17 line one more efficiently of course:

Sub FixHyperlinkDesc()
Dim h As Hyperlink
For Each h In ActiveSheet.Hyperlinks
h.TextToDisplay = Mid(h.TextToDisplay, InStrRev(h.TextToDisplay, "") + 1)
Next h
End Sub

Unless I'm missing something, there's a much easier way to do this.

Why doesn't George just type the text he wants to appear in the cell, right click, and select "Hyperlink"? The dialog box lets him select the file he wants to link.

I have hundreds of these on my budgeting spreadsheet, and these links point to files all over my system, across several different drives.