Highlighted
New Contributor

# 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

5 Replies
Highlighted

# Re: Change case and retain hyperlinks

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

# Re: Change case and retain hyperlinks

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

Highlighted

# Re: Change case and retain hyperlinks

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

# Re: Change case and retain hyperlinks

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

Highlighted

# Re: Change case and retain hyperlinks

Ellen, sorry, VBA is not my territory