CODEWITHSUNDEEP
excel
NoChance
NoChance

Reputation: 5752

How to trim the display text of hyperlink in Excel

I have some cells that contain hyperlinks. The hyperlinks have leading blanks. If I use TRIM, the result returned is just a text, not a hyperlink.

Is there a worksheet function (not vba) that can remove the leading blanks and retain the functionality of the hyperlink?

Example: ____Link1Text (where ___ are blanks and link1Text is a hyperlink in a cell).

Thank you.

Upvotes: 2

Views: 2367

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can try:

=HYPERLINK(TRIM(A1))

However, if Link1Text (without the leading spaces) is not the actual URL or full file path, then the Excel formula will not work. You'll need a VBA routine to extract the actual hyperlink address. If that is the case, then try this UDF (User Defined Function):

Function GetHyperlink(RG As Range) As String
    GetHyperlink = RG.Hyperlinks(1).Address
End Function

and then use this formula in the cell:

=HYPERLINK(GetHyperlink(A1),TRIM(A1))

Upvotes: 2

Related Questions