How to hide part of cell content?

Question

I want to hide part of the cell content in Excel like in MS Word where we can set the Hidden property of a selected text. The idea is to display a representative part while the underlying data can be retrieved by Range(...).Value2. Unfortunately, I don't see the Hidden property in Excel's Font object.

As an alternative, I thought some custom number format could be used. I found the content placeholder @, so I can hide, replace and pad text. However, I don't see an option to display content partially.

Is it possible to set the displayed part of the text in cells?

---

update Jun 26, 2022

At the moment I'm using a combination of event handling and number formatting. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Const Data = "A1"    ' data area to look for
Dim Common As Range
Dim Cell As Range
    Set Common = Intersect(Target, Range(Data))
    If Common Is Nothing Then Exit Sub
    For Each Cell In Common
        Cell.NumberFormat = GetFormat(Cell.Value2)
    Next Cell
End Sub
 
Private Function GetFormat(ByVal Value) As String
' Return the first five characters for demonstration purposes
Const Quote = """"
Dim Output as String
    Output = Left(Value, 5) & "..."
    GetFormat = ";;;" & Quote & Output & Quote
End Function

with this result:

As before, I hope this process can be simplified.

Answer 1

As you stated in your answer, there is no hidden Font property in Excel as one can find in Word. However, there are a variety of workarounds that would give the same functionality of "sort of hidden" (note that this is not a security feature). The use case for this in Word seems (to me) somewhat minimal except maybe to change the way something is printed and keeping notes? Comments and Notes in a cell would probably be more appropriate.

I was first thinking of using a font color, but that is functionally different than the Hide property because the Hide property removes the text rather than whites it out (so you would have a blank space the length of the hidden text).

Overall, I think that using an if statement within a string of your text with a global true/false driving cell would probably be the closest thing.

For example if cell A1 had either true or false, then any cell with the below formula could toggle between showing and not showing the hidden text:

="I have "&IF(A1,"a hidden text  ","")& "to think about."

If you never wanted the text to be visible, then you could just hard code it false.

="I have "&IF(False,"a hidden text  ","")& "to think about."

While not directly related to your question, if you were looking to have some notes within a cell formula that was a value (not text), the N function has been something I've used as it converts any text to zero, so it can be included after a calculation. Example:

=SUM(A:A)+N("This is a summation of all values in column A")