May 07 2019 01:02 PM
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
May 07 2019 01:24 PM
May 08 2019 07:40 AM - edited May 08 2019 10:15 AM
@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.
May 08 2019 08:13 AM
@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)) ) ) )
May 08 2019 12:34 PM - edited May 08 2019 01:26 PM
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
May 11 2019 08:28 AM