Forum Discussion

Ellen Finkelstein's avatar
Ellen Finkelstein
Copper Contributor
May 07, 2019

Change case and retain hyperlinks

I have a column that's all caps and every row is hyperlinked. I want to change the text to "Proper," that is, Title Case. It seems that the only way to do this is with the Proper function but when I ...
excel
Twifoo's avatar
Twifoo
Silver Contributor
May 07, 2019
Use the same hyperlink formula but wrap the friendly_name argument with PROPER.
Note that, in your case, Excel assumes the cell to its right as having the same format as it has.
  • Ellen Finkelstein's avatar
    Ellen Finkelstein
    Copper Contributor
    May 08, 2019

    Twifoo How do I "use the same hyperlink formula"? The hyperlinked text is already there and I don't want to have to recreate each hyperlink manually. And I did copy to the column to the right.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 08, 2019

      Ellen Finkelstein , not sure you may change in place without VBA, but you may add helper column with same links as

      =HYPERLINK(
   MID(FORMULATEXT(A1),
      SEARCH("(",FORMULATEXT(A1))+2,
      SEARCH(",",FORMULATEXT(A1))-SEARCH("(",FORMULATEXT(A1))-3),
   PROPER(
      MID(FORMULATEXT(A1),
         SEARCH(",",FORMULATEXT(A1))+2,
         LEN(FORMULATEXT(A1))-3-SEARCH(",",FORMULATEXT(A1))
      )
   )
)
      • Ellen Finkelstein's avatar
        Ellen Finkelstein
        Copper Contributor
        May 08, 2019

        Thanks, I'll try to figure out how to use the VBA (but it should be easier).

        From some research, it seems I need to make it a function?

        Put Public Function before it and End Function after it? Give it a name? Something about defining data types?

         

        I've done this and inserted it in a VBA module but it doesn't appear when I try to insert it as a function.

        Public Function sc
        =HYPERLINK(
        MID(FORMULATEXT(A1),
        SEARCH("(",FORMULATEXT(A1))+2,
        SEARCH(",",FORMULATEXT(A1))-SEARCH("(",FORMULATEXT(A1))-3),
        PROPER(
        MID(FORMULATEXT(A1),
        SEARCH(",",FORMULATEXT(A1))+2,
        LEN(FORMULATEXT(A1))-3-SEARCH(",",FORMULATEXT(A1))
        )
        )
        )
        End Function

        Ellen

Resources