Home

Change case and retain hyperlinks

%3CLINGO-SUB%20id%3D%22lingo-sub-543630%22%20slang%3D%22en-US%22%3EChange%20case%20and%20retain%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543630%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20that's%20all%20caps%20and%20every%20row%20is%20hyperlinked.%20I%20want%20to%20change%20the%20text%20to%20%22Proper%2C%22%20that%20is%2C%20Title%20Case.%20It%20seems%20that%20the%20only%20way%20to%20do%20this%20is%20with%20the%20Proper%20function%20but%20when%20I%20do%20that%2C%20I%20lose%20all%20of%20the%20hyperlinks%20--%20and%20it's%20especially%20evil%20because%20the%20text%20is%20still%20underlined%20and%20looks%20like%20it's%20hyperlinked%20--%20but%20it%20isn't.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20change%20the%20text%20to%20Proper%2FTitle%20case%20and%20retain%20the%20hyperlinks%3F%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EEllen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-543630%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-543720%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20case%20and%20retain%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543720%22%20slang%3D%22en-US%22%3EUse%20the%20same%20hyperlink%20formula%20but%20wrap%20the%20friendly_name%20argument%20with%20PROPER.%3CBR%20%2F%3ENote%20that%2C%20in%20your%20case%2C%20Excel%20assumes%20the%20cell%20to%20its%20right%20as%20having%20the%20same%20format%20as%20it%20has.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548693%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20case%20and%20retain%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243379%22%20target%3D%22_blank%22%3E%40Ellen%20Finkelstein%3C%2FA%3E%26nbsp%3B%2C%20not%20sure%20you%20may%20change%20in%20place%20without%20VBA%2C%20but%20you%20may%20add%20helper%20column%20with%20same%20links%20as%3C%2FP%3E%0A%3CPRE%3E%3DHYPERLINK(%0A%20%20%20MID(FORMULATEXT(A1)%2C%0A%20%20%20%20%20%20SEARCH(%22(%22%2CFORMULATEXT(A1))%2B2%2C%0A%20%20%20%20%20%20SEARCH(%22%2C%22%2CFORMULATEXT(A1))-SEARCH(%22(%22%2CFORMULATEXT(A1))-3)%2C%0A%20%20%20PROPER(%0A%20%20%20%20%20%20MID(FORMULATEXT(A1)%2C%0A%20%20%20%20%20%20%20%20%20SEARCH(%22%2C%22%2CFORMULATEXT(A1))%2B2%2C%0A%20%20%20%20%20%20%20%20%20LEN(FORMULATEXT(A1))-3-SEARCH(%22%2C%22%2CFORMULATEXT(A1))%0A%20%20%20%20%20%20)%0A%20%20%20)%0A)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548536%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20case%20and%20retain%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BHow%20do%20I%20%22use%20the%20same%20hyperlink%20formula%22%3F%20The%20hyperlinked%20text%20is%20already%20there%20and%20I%20don't%20want%20to%20have%20to%20recreate%20each%20hyperlink%20manually.%20And%20I%20did%20copy%20to%20the%20column%20to%20the%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549830%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20case%20and%20retain%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549830%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20I'll%20try%20to%20figure%20out%20how%20to%20use%20the%20VBA%20(but%20it%20should%20be%20easier).%3C%2FP%3E%3CP%3EFrom%20some%20research%2C%20it%20seems%20I%20need%20to%20make%20it%20a%20function%3F%3C%2FP%3E%3CP%3EPut%20Public%20Function%20before%20it%20and%20End%20Function%20after%20it%3F%20Give%20it%20a%20name%3F%20Something%20about%20defining%20data%20types%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20this%20and%20inserted%20it%20in%20a%20VBA%20module%20but%20it%20doesn't%20appear%20when%20I%20try%20to%20insert%20it%20as%20a%20function.%3C%2FP%3E%3CP%3EPublic%20Function%20sc%3CBR%20%2F%3E%3DHYPERLINK(%3CBR%20%2F%3EMID(FORMULATEXT(A1)%2C%3CBR%20%2F%3ESEARCH(%22(%22%2CFORMULATEXT(A1))%2B2%2C%3CBR%20%2F%3ESEARCH(%22%2C%22%2CFORMULATEXT(A1))-SEARCH(%22(%22%2CFORMULATEXT(A1))-3)%2C%3CBR%20%2F%3EPROPER(%3CBR%20%2F%3EMID(FORMULATEXT(A1)%2C%3CBR%20%2F%3ESEARCH(%22%2C%22%2CFORMULATEXT(A1))%2B2%2C%3CBR%20%2F%3ELEN(FORMULATEXT(A1))-3-SEARCH(%22%2C%22%2CFORMULATEXT(A1))%3CBR%20%2F%3E)%3CBR%20%2F%3E)%3CBR%20%2F%3E)%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3EEllen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560834%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20case%20and%20retain%20hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243379%22%20target%3D%22_blank%22%3E%40Ellen%20Finkelstein%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEllen%2C%20sorry%2C%20VBA%20is%20not%20my%20territory%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ellen Finkelstein
New 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies