Change case and retain hyperlinks

Copper Contributor

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

5 Replies
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.

@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.

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

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

@Ellen Finkelstein 

 

Ellen, sorry, VBA is not my territory