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 do that, I lose all of the hyperlinks -- and it's especially evil because the text is still underlined and looks like it's hyperlinked -- but it isn't. 

 

Is there any way to change the text to Proper/Title case and retain the hyperlinks?

Thanks,

Ellen

excel

5 Replies

  • 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))
      )
   )
)

Resources